CREATE OR REPLACE FUNCTION some_number() RETURNS integer AS $$
quantity integer := 30;
quantity := 42;
$$ 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:
You should get a result set of one row and one column, with the number "42".
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.