PostgreSQL allows you to use procedural languages, similar to PL/SQL in Oracle.

Creation

Simple Procedure

pollux=> CREATE FUNCTION "name" (integer)
RETURNS text AS
' SELECT username FROM users WHERE uid=$1'
LANGUAGE 'sql';
CREATE FUNCTION


pollux=> SELECT * FROM name(1);
  name
---------
 wzdftpd
(1 row)

Adding elements

pollux=> CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
pollux-> AS 'SELECT $1 + $2'
pollux-> LANGUAGE SQL;
CREATE FUNCTION


pollux=> SELECT add_em(3,7);
 add_em
--------
     10
(1 row)

Dropping a function

To drop a function, you must use function name and arguments (not just the name):

pollux=> DROP FUNCTION add_em (IN x int, IN y int, OUT sum int);
DROP FUNCTION

Languages

PL/pgSQL

Using psql

Before using other languages, you must load the proper libraries, as superuser. You must be connected to the correct database (do not do this on template1, unless you want all databases create with this template to have the language enabled).

pollux=# CREATE FUNCTION plpgsql_call_handler ()
pollux-# RETURNS OPAQUE
pollux-# AS '/usr/lib/postgresql/8.2/lib/plpgsql.so'
pollux-# LANGUAGE 'C';
CREATE FUNCTION

This only creates the handler; the language itself must also be added with the CREATE LANGUAGE command. Here is the syntax to add PL/pgSQL to a database:

pollux=# CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
pollux-# LANCOMPILER 'PL/pgSQL'
pollux-# ;
NOTICE:  using pg_pltemplate information instead of CREATE LANGUAGE parameters
CREATE LANGUAGE

Using createlang

You can create the language using the command-line tool createlang. Change current user to become postgres:

# su - postgres
postgres:~$ createlang plpgsql pollux

Creating a simple PL/pgSQL function

After adding the language, you can create a simple function, which adds three elements (whatever their type is), and returns the result:

pollux=> CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
result ALIAS FOR $0;
BEGIN
result := v1 + v2 + v3;
RETURN result;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION


pollux=> select add_three_values(1,2,3);
 add_three_values
------------------
                6
(1 row)