SQL/PSM

Z PostgreSQL
Skočit na navigaci Skočit na vyhledávání

SQL/PSM is a mystic language without any information. The bible is ISO/IEC 9075-4. Its first name was only PSM, next PSM-92, PSM-96 and then SQL/PSM. Older versions are incompatible with SQL/PSM. The author of SQL/PSM is propably Jim Melton. Some RDBMS implements some subset of SQL/PSM. Famous is the implementation in DB2. I don't know why, but the paradise of SQL/PSM is Sweden. SQL/PSM is implemented in Mimer, Solid, MySQL. You can find SQL/PSM suppport in daffodildb too. SQL/PSM support Czech 602SQL Server RDBMS too. OOD system Matisse use SQL/PSM language for objects’ methods. Manual in Czech Příručka SQL/PSM or English language — SQL/PSM Manual.

SQL/PSM is supported

SQL/PSM support in PostgreSQL

PL/pgPSM is based on the syntax of SQL/PSM (SQL:2003) with some differences. Current version isn't usable for production. Main goals are education and collecting information about SQL/PSM now. This version is based on the PL/pgSQL interpreter (with modified syntax parser's part for SQL/PSM). Currently this project is external and a little bit independent of core PostgreSQL. You can find it on pgFoundry. Hackers would prefer to completely rewrite PL interpreter (the source code is a little bit dirty), but I believe some work on the PL API is needed before this rewrite. Current PL runtime is fast and well tested.


Discussions from PostgreSQL Hackers archive

Questions

  • which stmts signal NOT FOUND (??DELETE??)

Difference from std.

  • PLpgPSM don't support procedures (and unbounded SELECTs ??is part of std??),
  • for SRF is supported RETURN NEXT table expression is supported,
  • every not VOID or with OUT params function have to finish with RETURN,
  • default for SQL/PSM is SECURE DEFINER (SECURE CALLER in PostgreSQL),
  • dynamic SQL EXECUTE IMMEDIATE INTO (db2)
  • SELECT INTO variables columns ... (PL/pgSQL) .. is correct (from notes to ISO is implementation depend),
  • PERFORM (will be replaced CALL statement),
  • Signal parameters (condition information item name) add DETAIL, LEVEL, HINT (from PostgreSQL), originally only MESSAGE_TEXT, isn't possible signal '00000' (and possible trapp it),
  • for debug output statement PRINT.
  • CONTINUE HANDLER is fast for warnings; if it's used for EXCEPTION is slow (every statement generate savepoint),
  • EXIT handler is prohibited for exception (allowed for warnings), only UNDO handler with BEGIN ATOMIC block is allowed,
  • PostgreSQL use different set of function's attributes
    • DETERMINISTIC ~ IMMUTABLE, NON DETERMINISTIC ~ VOLATILE,
    • SQL, NO SQL ~ no relevant PostgreSQL attributes,
  • EXECUTE [INTO varlist] [USING varlist],
  • NULL statement, .. do nothing,
  • refcursor (from plpgsql), missing support WITH HOLD, WITH RETURN cursors,
  • supported unbounded (refcursors) OPEN name FOR ... (from plpgsql),
  • attr NO SCROLL isn't supported (is implicit),
  • support SQLSTATE variable (from DB2)

Diff from MySQL SQL/PSM language

  • unsupported unbound SELECT statements,
  • comma separated predicates in simple CASE statement,
  • SIGNAL statement,
  • PREPARE can contain any expression,
  • unsupported CALL statement
  • support standard multiassign statement (list version in mysql is nonstandard),

Difference from PL/pgSQL

  • syntax,
  • all function's arguments are writeble.
  • exception level depends on SQLSTATE
    • 00000 INFO
    • 01000 WARNING
    • 02000 WARNING (NO DATA or NOT FOUND)
    • xxxxx EXCEPTION
  • cursor statements are ansi compatible
    • FETCH can specify direction
    • OPEN cannot use parameters (but can specify unbound cursor)
  • composed types variables are default NULL and can be NULL
BEGIN
DECLARE a mytype;
  SET a = mytype(); -- default constructor
  a.prop1(10); -- default its eq SET a.prop = 10
END;

Supported statements

plpgpsm samples:

Needs

  • cleaning of code, most of changes are in gram.y, other code is still plpgsql, remove a lot of dead code,
  • documentation, translating Příručka SQL/PSM from czech to english,
  • regress tests,
  • testing,
  • patch core (scrollable cursors, trappable warnings)Can wait,
  • any information about SQL/PSM (people, history, implementations, ...)
  • has anybody "Understanding SQL's Stored Procedures: A Complete Guide to SQL/PSM"?,
  • yep, there is a book http://www.amazon.com/Understanding-Sqls-Stored-Procedures-Management/dp/1558604618 (Only for USA :-( )

ToDo

  • exception trapping (continue and exit handlers for exceptions).
  • cursors and FOR stmt, different directions for fetch statement,
  • parametrized prepared statement and EXECUTE USING,
  • all PostgreSQL variables can be accessed via GET DIAGNOSTIC and modified via SET,
  • resignal (warning can be handled outer???),
  • states in handler's declarations are disjoint,
  • order of handlers, the most specific handlers has bigger priority,
  • close unclosed cursors declared in block when leave block,
  • all users signals has only one sqlstate (if sqlstate isn't defined by SQLSTATE) and is divided by conditional_name,
  • any expression can signal NOT FOUND (set a = 'aaaa' || (SELECT a FROM b WHERE a = 10))
  • GET STACKED DIAGNOSTIC,
  • merge patch scrollable cursor for 8.3,
  • holdable cursor support (have to be supported SPI),
  • composed types variables are default NULL and can be NULL

Questions

  • SECURE DEFINER or SECURE CALLER will be default? SECURE CALLER - reason is compatibility with others PL envoronments,
  • reformat dump: dump dummy functions, dump tables, dump full functions (for statement)
  • ANSI SQL SRF functions RETURNS TABLE(c1,c2,c3) ~ (OUT c1, OUT c2, OUT c3) RETURNS SETOF RECORD

Errors

  • cannot redefine variable in nested block

Installation

PL/pgPSM is supported for PostgreSQL versions 8.1, 8.2, 8.3.

newer 0.2.0

or teoreticly (not testested) can be compiled without postgresql source tree (you need develop libraries)

  • extract somewhere
  • cd plpgpsm
  • make -f Makefile.pgxs
  • make -f Makefile.pgxs install
  • psql anydb
  • \i plpgpsm.sql

older 0.2.0

  • download src from http://pgfoundry.org/frs/download.php/1236/plpgpsm-01.tar.gz
  • extract into src/pl/plpgpsm (in PostgreSQL 8.2 source tree) .. similar plpgsql
  • modify src/pl/Makefile .. on line 29 add DIRS += plpgpsm
  • go to src/pl/plpgpsm/src amd make install
  • restart postgresql server
  • in psql do (as superuser)
  INSERT INTO pg_pltemplate 
    VALUES('plpgpsm',true,'plpgpsm_call_handler','plpgpsm_validator','$libdir/plpgpsm', NULL)
  • make check
  • create lang plpgpsm your_db
  • use it


Questions, ideas: Pavel Stehule (okbob on freenode, ICQ: 138051654, stehule@kix.fsv.cvut.cz)

Conformance with ANSI

  • Feature P002, “Computational completeness”
  • Feature P005 "Qualified SQL variable references"
  • Feature P006, “Multiple assignment”
  • Feature P007, “Enhanced diagnostics management"
  • Feature P008, “Comma-separated predicates in simple CASE statement”
Citováno z „http://postgres.cz/index.php?title=SQL/PSM&oldid=215“