DB2 SQL Procedures on Postgres? Yes, why not?

I ported PL/pgPSM for PostgreSQL 8.4 this week. PL/pgPSM is implementation of SQL/PSM language to PostgreSQL space. SQL/PSM is procedural language from ANSI SQL standard. DB2 or MySQL use it. PL/pgPSM has some features from IBM implementation, but it isn't fully compatible. It isn't possible - PostgreSQL is little bit different than DB2. Relation between PL/pgPSM and SQL PL is like relation between PL/pgSQL and PL/SQL.

Installation

1. download source code,
2. extract it in PL directory in PostgreSQL 8.4 source code tree
3. compile and install it

   [pavel@localhost Desktop]$ mv plpgpsm.tgz ../src/pgsql/src/pl/
   [pavel@localhost Desktop]$ cd  ../src/pgsql/src/pl/
   [pavel@localhost pl]$ tar xvfz plpgpsm.tgz
   plpgpsm/Makefile
   plpgpsm/src/
   plpgpsm/src/pl_gram.c
   plpgpsm/src/pl_exec.c
   plpgpsm/src/gram.y
   plpgpsm/src/pl_funcs.c
   plpgpsm/src/plpgpsm.h
   plpgpsm/src/y.tab.h
   plpgpsm/src/sql/
   plpgpsm/src/sql/plpgpsm.sql
   plpgpsm/src/INSTALL.plpgpsm
   plpgpsm/src/Makefile
   plpgpsm/src/pl_handler.c
   plpgpsm/src/plerrcodes.h
   plpgpsm/src/scan.l
   plpgpsm/src/y.tab.c
   plpgpsm/src/pl_comp.c
   plpgpsm/src/expected/
   plpgpsm/src/expected/plpgpsm.out
   plpgpsm/src/pl_gram.h
   plpgpsm/src/pl_scan.c
   [pavel@localhost pl]$ cd plpgpsm/src/
   [pavel@localhost src]$ make all
   ...
   [pavel@localhost src]$ su
   Heslo:
   [root@localhost src]# make install
   /bin/sh ../../../../config/mkinstalldirs '/usr/local/pgsql/lib'
   /bin/sh ../../../../config/install-sh -c -m 755  plpgpsm.so '/usr/local/pgsql/lib/plpgpsm.so'
   



4. register pl handler:
   

   [pavel@localhost src]$ psql template1
   psql (8.4devel)
   Type "help" for help.
   
   template1=# insert into pg_pltemplate
   select 'plpgpsm','t'::boolean, 't'::boolean, 'plpgpsm_call_handler','plpgpsm_validator','$libdir/plpgpsm',NULL
   from (
   select 'plpgpsm'
   except all
   select tmplname
   from pg_pltemplate
   ) a;
   INSERT 0 0
   



5. Check it:
   

   
   [pavel@localhost src]$ pwd
   /home/pavel/src/pgsql/src/pl/plpgpsm/src
   [pavel@localhost src]$ make installcheck
   make -C ../../../../src/test/regress pg_regress
   make[1]: Entering directory `/home/pavel/src/pgsql/src/test/regress'
   make[1]: `pg_regress' is up to date.
   make[1]: Leaving directory `/home/pavel/src/pgsql/src/test/regress'
   ../../../../src/test/regress/pg_regress --inputdir=. --psqldir= --dbname=pl_regression --load-language=plpgpsm plpgpsm
   (using postmaster on Unix socket, default port)
   ============== dropping database "pl_regression"      ==============
   DROP DATABASE
   ============== creating database "pl_regression"      ==============
   CREATE DATABASE
   ALTER DATABASE
   ============== installing plpgpsm                     ==============
   CREATE LANGUAGE
   ============== running regression test queries        ==============
   test plpgpsm              ... ok
   
   =====================
   All 1 tests passed.
   =====================

Usage

1. Read some documentation http://www.sqlpl-guide.com/ and http://www.pgsql.cz/index.php/SQL/PSM_Manual, http://www.pgsql.cz/index.php/MySQL5:_Gentle_introduction_to_stored_procedures_of_MySQL5
2. Play with it and use it :)

   postgres=# CREATE OR REPLACE FUNCTION foo2(a integer)
   postgres-# RETURNS void AS
   postgres-# $$
   postgres$#   BEGIN
   postgres$#     DECLARE i integer DEFAULT 1;
   postgres$#     WHILE i <= a
   postgres$#     DO
   postgres$#       PRINT i;
   postgres$#       SET i = i + 1;
   postgres$#     END WHILE;
   postgres$#   END
   postgres$# $$ LANGUAGE plpgpsm;
   CREATE FUNCTION
   postgres=# select foo2(3);
   NOTICE:  1
   NOTICE:  2
   NOTICE:  3
   foo2 ------
   
   (1 row)