Extending the Database: PL/pgSQL

* PostgreSQL offers two interpreted languages in which one can write SQL functions: PL/pgSQL and PL/tcl. More can be added by the user by coding the appropriate stub.

* PL/pgSQL Example:

As user postgres:

test=> CREATE FUNCTION plpgsql_call_handler ()
test-> RETURNS OPAQUE
test-> AS '/usr/lib/pgsql/plpgsql.so'
test-> LANGUAGE 'c' \g
CREATE
test=>
test=> CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
test-> HANDLER plpgsql_call_handler
test-> LANCOMPILER 'PL/pgSQL' \g
CREATE

As user shlomi:

test=> CREATE FUNCTION num_descendants (int4)
test-> RETURNS int4 AS
test-> '
test'>     DECLARE
test'>         key ALIAS for $1;
test'>         num int4;
test'>         child RECORD;
test'>     BEGIN
test'>         num := 0;
test'>         FOR child IN SELECT c FROM par_kids WHERE p = key LOOP
test'>             num := num + 1 + num_descendants(child.c);
test'>         END LOOP;
test'>         RETURN num;
test'>     END;
test'> '
test-> LANGUAGE 'plpgsql' \g
CREATE
test=> SELECT num_descendants(1) \g
num_descendants
---------------
              9
(1 row)