Pages

Friday, April 22, 2011

PSM (zero) is completed, and needs you

Hello,

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
    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 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.

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

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

2 comments:

  1. Very pleased to see this. Well done.

    I'm sure many people would love to help, but before they do, we need to clarify the Copyright, Licence and contributions policy. I can't find any mention of licence on any of the files, which makes me a little nervous. Right now, I wouldn't advise others to use it because of this.

    Can you clarify those things please?

    ReplyDelete
  2. [Simon] - It will be released under BSD licence - the final decision should do my employer - but I don't expect any problems. CZ.NIC http://www.nic.cz/ supports open source projects and it is a PostgreSQL user - Czech Domain Registry uses PostgreSQL as database backend.

    It's designed for integration to core,I hope in 9.2 or 9.3 - there is lot of work and it need a relative significant enhancing of SPI api (for clean implementation). Code from git working with current GIT Postgres.

    ReplyDelete