Saturday, December 27, 2008

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)