PSM (zero) project is completed and needs you

Original author: Pavel Stehule
  • Transfer
Yesterday, Pavel Stehule completed work on implementing the SQL / PSM procedural language for PostgreSQL.

At the moment, the language supports everything you need:
  • simple things - arrays, composite types (composites), triggers;
  • additional - functions that return a table, IN / OUT parameters;
  • SQL / PSM features - warnings, exception handlers (most of them based on SQLCODE), SIGNAL and RESIGNAL statements;
  • some features of DB2 and MySQL are multi assign statements, support for magic variables SQLSTATE and SQLCODE.


Some examples:

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 developed as a replacement for the native PL / pgSQL . It was developed as an alternative language with a slightly different philosophy:

  • full validation of nested SQL at 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 runtime errors when using PL / pgSQL can be detected at compile time in the case of using PSM.

Task list:
  • Complete code analysis, add comments - are there any volunteers?
  • Performance optimization.
  • Debugging error messages.


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

Also popular now: