Proposal: PL/pgPSM for 9.3

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