My first Postgres Function (PL/pgSQL)

After reading this O'Reilly article and the Postgres doc, I made my first function to run inside the Postgres server. This code is serves no real purpose, but is simple and harmless, so it makes a good example and first experiment.

CREATE OR REPLACE FUNCTION some_number() RETURNS integer AS $$

DECLARE
    quantity integer := 30;
BEGIN
    quantity := 42;
    RETURN quantity;
END;
$$ LANGUAGE plpgsql;


You can try it. Copy that text, paste it into the ‘Query Tool’ of pgAdmin, and execute it. If it works, you'll get a response like this:
Query returned successfully with no result in 230 ms.

To call the function, execute this line:
SELECT some_number();

You should get a result set of one row and one column, with the number "42".

NOTES

Notice that the example above is a single line of SQL. From the SQL interpreter's perspective there is a single line, with 4 key words ("CREATE…", "RETURNS", "AS", "LANGUAGE") and a semicolon at the end. The DECLARE-BEGIN-END, our function's source code, is the content of a single value delimited by the double dollar signs "$$".

You can call "CREATE FUNCTION" or "CREATE OR REPLACE FUNCTION". The first is safer, so you don't unintentionally replace a pre-existing funtion. After re-writing your code, use the second command to replace existing code on you Postgres server.

Be careful in naming your function, to avoid using reserved labels.

The beginning and end of the function is defined by the "$$". The beginning before the dollar signs declares the signature of the function (name, arguments, and return type). The ending after the question declares the language used to write the function. Together, that beginning and ending make up a single statement as perceived by the SQL interpreter, with a semicolon at the end. The middle part, the source code of the function, is delimited by the "$$" to prevent execution by the SQL interpreter. Instead of execution, the middle block is handed over to the PL/pgSQL module.

There's a lengthy tutorial to read.

This function is written in Postgres' own scripting language, PL/pgSQL. You can write server-side code in several other languages such as Java, Ruby, Python, Perl, and more.