Friday, April 22, 2011

PSM (zero) is completed, and needs you


yesterday I finished work on prototype implementation of SQL/PSM language for PostgreSQL. Now, this language supports all necessary features:

  • basic features - arrays, composite types, triggers
  • advanced features - table returned functions, IN/OUT variables
  • SQL/PSM specific features - warning, exceptions (general, SQLCODE) handlers, SIGNAL, RESIGNAL statements
  • some DB2 or MySQL features - multi assign statement, support for magic SQLSTATE and SQLCODE variables
some samples:
create or replace function test74_2()
returns text as $$
begin atomic
  declare not_found condition for sqlstate '03000';
  declare undo handler for not_found
      declare xx, yy text;
      get stacked diagnostics xx = condition_identifier, yy = returned_sqlstate;
      return xx || ' Signal handled ' || yy;
  signal not_found;
$$ language psm0;

create or replace function test66(a int, out r int) as $$
  declare continue handler for sqlstate '01002'
    set r = r + 1;
  declare continue handler for sqlstate '01003'
    set r = r + 2;
  set r = 0;
x: while a > 0 do
     if a % 2 = 0 then
       signal sqlstate '01002';
       signal sqlstate '01003';
     end if;
     set a = a - 1;
   end while;
$$ language psm0;

This PL isn't designed as an replacement of PL/pgSQL. It is designed as second language with little bit different philosophy and goals.

  • complete validation of embedded SQL in compile time,
  • result of embedded SQL is transformed to target type early
The main goal is a searching bugs inside embeded SQL early - usually in compile time. This goal changes a rules in languages. PSM is very static language. There are more tasks, that should be solved via dynamic SQL than in PL/pgSQL. On second hand, lot of PL/pgSQL runtime bugs can be detected in PSM in compile time.

  • Complete revision and review, more comments - any volunteers ?
  • Performance optimizations,
  • Cleaning of error messages

Source code is available from github.
I invite any help!