Hello I am sending a proposal for PSM language support. It is early maybe. I would to have a patch for first 9.3 commitfest.
Proposal PL/pgPSM I propose to integrate a PSM language into the core. This language is defined as part of ANSI SQL - SQL/PSM and is used in some well known databases like DB2, Terradata and some other less known RDBMS like MonetDB. A The proposed implementation is based on the same architecture as the current PL/pgSQL interpreter - interpretation of AST with integration of an SQL parser and expression executor. Reasons why to use same architecture are: reuse some parts of interpreter and well experience with the current interpreter. One year ago I wrote a PSM compiler to pcode and pcode interpreter - PSM0. This project showed that using pcode doesn't carry any speedup over AST interpret - the bottleneck was an expression execution - and PostgreSQL executor is the part that we would not substitute, so we can use a simpler and well known AST interpreter. Language is specified by the SQL standard SQL/PSM - so I'll describe main differences against PL/pgSQL: * Exception handlers are subroutines (continue handlers). * Warnings can be handled like an exception - handling warnings is important and well known pattern BEGIN DECLARE success boolean DEFAULT true; DECLARE _x varchar; DECLARE c CURSOR FOR SELECT x FROM tab; DECLARE CONTINUE HANDLER FOR NOT FOUND SET success = false; OPEN c; FETCH c INTO _x; WHILE success DO -- process _x FETCH c INTO _x; END WHILE; CLOSE c; END; * Declaration of variables should be based on SQL query result - and because we would have to execute and check queries only at runtime, we have to use a little bit different rules for variables - variables should not be declared before they are used - it is possible, because there are different syntax for assign statement. Automatic variables should be read only. BEGIN DECLARE s text; FOR SELECT curse FROM courses DO SET s = COALESCE(s || ',' curse, course); END FOR; Other differences are minor. Request: * reusing important parts of plpgsql executor - simple expressions, using variables, assign results to variables * support “check function” statement * be as near to the standard as possible * implement well known patterns that are not in standard, but that have impact on speed or usability and are used in well known implementations (SQLCODE variable, multi assign). -- little bit more effective iteration (well known pattern) BEGIN OPEN c; FETCH c INTO _x; WHILE SQLCODE = 0 DO -- process _x; FETCH c INTO _x; END WHILE; CLOSE c; END; * check strictly expression syntax don't allow plpgsql's " var := SELECT 10; var := a FROM tab " and similar, only "set var = 10; or set var = (SELECT 10); or set var = (SELECT a FROM tab) should be allowed LIMITS: * PostgreSQL doesn't support procedures - statement CALL will not be supported (I would like to see CALL statement as PostgreSQL statement, not only PSM emulation) * I don't plan to implement PSM into SQL parser in first step. Reason - this feature requries an introduction of a DELIMITERs (like MySQL or DB2). This concept is not adapted in PostgreSQL and PostgreSQL design is more user friendly for work from command line tools. This can be enhanced in future, if we find a way to extend the bison parser. Using $$ delimiters for function body doesn't block some future enhancing. Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers