Pages

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)

3 comments:

  1. I love your postings, Pavel.
    My personal idea in regards to the compatibility between PGSQL and "the rest of the multiverse" is that I would prefer to have a better PGSQL rather than a more compatible one.
    For two reasons:
    1. PGSQL is the most advanced opensource RDBMS. This is more than a tagline, is a real distinctive character.
    2. I would prefer a (kind of) compiler between PL/whatEVER and, say, PL/pgSQL. Compile once, run forever.

    This means I would prefer to concentrate resources on an ever enhancing RDBMS as far as performances and features. PL/pgSQL has enough expressivity to compare with any other PL out there. A translator tool would maybe better!

    ReplyDelete
  2. That's great Pavel.

    I aggree to consider a translator tool can be a good approach to avoid too many languages.
    But in this case, I understand it deals with ANSI SQL standard. And respect of standards is an important argument to convinced IT-Director to choose PostgreSQL.

    I will probably have to work next year for one of my customer to study the migration of a DB2 database having stored procedures into PostgreSQL. This gives me an additional argument to face the IBM camp !

    ReplyDelete
  3. Hello,

    I don't see any sense for compiler from pl/* to plpgsql - from pl/* to C is different story. Now, plpgpsm is modified plpgsql interpret. So I hope, so in future plpgsql will be more strongly modularized and so lot of code should be shared with plpgpsm.

    plpgsql interpret is good, fast and reliable - but it's little bit old too - so we invite any experiments about some changes - p-code using, test parrot and similar.

    Current plpgpsm's implementation hasn't big ambition - just open door and show to PostgreSQL's users language from standard. Well implementation needs some set of users and only a few people know SQL/PSM, Fakt, so PL/pgPSM is based on core of PL/pgSQL carries same speed, behave, stability like PL/pgSQL and same disadvantages.

    To Philippe - I am enjoy, so my work should be useful for someone.

    ReplyDelete