initial implementation of SQL/PSM language for PostgreSQL
C Perl
Permalink
Failed to load latest commit information.
Makefile
README
TODO
compiler.c
executor.c
generate-plerrcodes.pl
gram.c
gram.h
gram.y
handler.c
plpsm0.sql
psm.h
scanner.c
test.sql

README

PLPSM is formal language specified by ANSI SQL - SQL/PSM part. This language is used in some
comercial databases with name SPL - Stored Procedure Language. The main targets of PLPSM:

* implementation that is near to standard
* be simple, clean and robust
* embeded SQL is validated in compile time
* using a early casting and using only enabled conversions

The main differnce between PLPSM and PL/pgSQL is:

* more precise validation of embeded SQL and expressions:

    PLPSM checkers can find more errors in compile time. Other - all database objects must be
    well identified in compile time. There are not possible a generic types as RECORD, there 
    are no possible polymorphic types, there are no possible static DDL statements.

* PLPMS has simpler expression executor than PL/pgSQL has:

    Some procedures that uses a lot of expressions can be two-five times slower. Updating a field
    of composite type is slower than in PL/pgSQL. On second hand - using a composite values
    can be faster, execution of embeded SQL can be faster in PLPSM. PLPSM uses only common 
    SPI interface, that doesn't allow a execution of "simple" queries. That is significantly
    faster - 2-5x. So PLPSM in this moment isn't language for high NOT SQL calculations. Use
    PL/pgSQL, PLPerl, PLPython or C instead. For typical stored procedures - based on embeded
    SQL the execution speed is same.

* PLPSM is based on compilation to simple virtual machine, PL/pgSQL is based on interpretation of
  semantic tree. 

    Usually this factor isn't important. For stored procedures a main factor is a speed of
    embeded SQL execution. Both environments uses a same API - SPI - so in typical use cases a 
    execution of stored procedures in these enveroments will be same or similar. A interpretation
    allows a some tricks - but the one significant goal of PLPSM is a disabling these techniques.
    Code in PLPSM should be simply ported to other databases with SQL/PSM support so some ugly
    tricks are not allowed (by design). A compilation allows deeper checking and early error's 
    localization.

* PLPSM use a early checking of trigger's variables NEW and OLD validity. 

    You can use a invalid varible NEW or OLD (in a given context) in PL/pgSQL, but you can't
    to access a context of these variables. PLPSM is more strict, you can't to use a trigger
    variable without adequate context. It is checking on start of trigger. 

* PLPSM doesn't use a prepared plans for cursors.

    PL/pgSQL uses a cached plans for cursors, queries and expressions. PLPSM uses
    cache only for expressions and queries. It's mean, so inner loops over cursors
    can be slower, but there less possibility of using a slow plan. 

* PLPSM has more detailed exception report.

    PL/pgSQL shows only statement name and line with broken statement. PLPSM shows a
    variables, their types and content, shows a function parameters and line. You can
    see a complete stack.

What is same between PLPSM and PL/pgSQL:

* lot of routines, that ensure perfect syntax error diagnostic are taken from PL/pgSQL
  source code.


What is supported:

P001 Stored modules
P002 Computational completeness
P004 Extended CASE statement
P005 Qualified SQL variable references
P006 Multiple assignment
P007 Enhanced diagnostics management (partial, only with diagnostics info supported by PostgreSQL)