I want to know how to do this, because after looking around, all I found are long and complicated ways to do this seemingly easy job. In MySQL, we had:
DELIMITER //
...function start
INSERT INTO table VALUES (1);
...function end//
DELIMITER ;
which was really useful when we wanted to use stored procedures or funtions. I'm looking for the equivalent of this in PostgreSQL, if there is one.
Thanks in advance!
The issue with the MySQL DELIMITER plays out quite differently in PostgreSQL. (First of all, most programmers would not use the psql command line to enter a function; instead you'd write a SQL script file and execute that, but this is besides the point of your question.)
In PostgreSQL the function body is basically a long string. That string can be delimited by basically anything that does not conflict with anything else. In the SQL standard - and also in the PostgreSQL documentation - single quotes ' are used, instead of just starting the function body unquoted like in MySQL. So when you write a function header (CREATE FUNCTION ...) you would write a quote to start the function body before you write any semi-colon to terminate statements inside the function. That means that the MySQL problem with the semi-colon does not exist in PostgreSQL: the parser is just reading a string and waiting for that string to be completed with a closing quote and then the command terminated with a semi-colon.
There is more to it, however.
In PostgreSQL the convention is to use $$ or $anything_goes_here$, the so-called dollar quoting, instead of a ' to start the function body. The reason for this is that it avoids having to escape embedded quotes in the function body. See the docs for an explanation of this feature.
The generic function definition looks somewhat like this:
CREATE FUNCTION my_func(arg1 data_type, ...) RETURNS data_type AS $body$
INSERT INTO foo(my_column) VALUES(arg1)
RETURNING some_column;
$body$ LANGUAGE sql;
Note that there are also other programming languages in PostgreSQL - most notably PL/pgSQL, the built-in procedural languages, but also variants of Perl, Python, Tcl, C, ... - and all use the same function definition syntax (as per the SQL standard) including the delimiters, only the function body will differ.
You can use different delimiters for different functions too, but the opening and closing delimiters of a single function need to match.