The PSM (zero) project is complete and needs you

Pavel Stehule finished work yesterday on implementing the procedural SQL / PSM language for PostgreSQL.

At the moment, the language supports everything you need:

• simple things - arrays, compound types (composites), triggers;
• additional - functions returning the table, IN / OUT parameters;
• SQL / PSM features — warnings, exception handlers (mostly based on SQLCODE), SIGNAL and RESIGNAL statements;
• Some features of DB2 and MySQL include multi assign statements, support for the magic variables SQLSTATE and SQLCODE.

Some examples are:

  create or replace function test74_2 ()
 returns text as $$
 begin atomic
   declare not_found condition for sqlstate '03000';
   declare undo handler for not_found
     begin
       declare xx, yy text;
       get stacked diagnostics xx = condition_identifier, yy = returned_sqlstate;
       return xx ||  'Signal handled' ||  yy;
     end;
   signal not_found;
 end;
 $$ language psm0;

 create or replace function test66 (a int, out r int) as $$
 begin
   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';
      else
        signal sqlstate '01003';
      end if;
      set a = a - 1;
    end while;
 end;
 $$ language psm0;

')
This language was not designed as a replacement for native PL / pgSQL . It was developed as an alternative language with a slightly different philosophy:

• complete validation of nested SQL at the compilation stage;
• Early conversion of nested SQL results to target types.

The main advantage of the language is an early search for errors in the use of nested SQL expressions - usually at the compilation stage. This feature is exceptional. PSM is a very static language. On the one hand, more gestures are needed to work with dynamic SQL queries than if PL / pgSQL were used. On the other hand, many run-time errors with PL / pgSQL can be detected at compile time when using PSM.

Task list:

• Complete the code analysis, add comments - are there volunteers?
• Performance Optimization.
• Debugging error messages.

Source code is available on github . Any help is welcome.