imported>WikiSysop: /* Difference from PL/pgSQL */

2007-11-04T20:43:32Z

Difference from PL/pgSQL

Nová stránka

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 ===
* IBM DB2
* MySQL (version 5.0 and higher),
* Solid
* Mimer
* Sybase iAnywhere (Advantage Database Server 8.0, November 2005), http://www.extendedsystems.nl/web/content.aspx?key=F7144BCC5E1B10699707CC72D9B4A3C8
* 602SQL server
* Daffodil DB http://www.daffodildb.com/daffodildb-psm.html
* ElevateDB

== 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 [http://pgfoundry.org/projects/plpsm/ 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 ===
* http://archives.postgresql.org/pgsql-hackers/2005-06/msg01467.php
* http://archives.postgresql.org/pgsql-hackers/2005-06/msg01466.php

=== Questions ===
* which stmts signal NOT FOUND (??DELETE??)

=== Difference from std. ===
* PLpgPSM don't support procedures (and unbounded SELECTs ??is part of std??),
* <strike>for SRF is supported RETURN NEXT</strike> 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, <strike>LEVEL</strike>, 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),
* <strike>EXIT handler is prohibited for exception (allowed for warnings), only UNDO handler with BEGIN ATOMIC block is allowed</strike>,
* 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],
* <strike>NULL statement, .. do nothing</strike>,
* refcursor (from plpgsql), missing support WITH HOLD, WITH RETURN cursors,
* supported unbounded (refcursors) OPEN name FOR ... (from plpgsql),
* <strike>attr NO SCROLL isn't supported (is implicit)</strike>,
* 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
<pre>
BEGIN
DECLARE a mytype;
SET a = mytype(); -- default constructor
a.prop1(10); -- default its eq SET a.prop = 10
END;
</pre>

== Supported statements ==
plpgpsm samples:
* [[basic statements SQL/PSM samples]],
* [[dynamic SQL in SQL/PSM samples]],
* [[dynamic cursors support samples]],
* [[SQL statements in SQL/PSM samples]],
* [[Exception handling and signaling in SQL/PSM samples]],
* [[cursors in SQL/PSM samples]],
* [[table expression in RETURN statement for SRF functions]].
* [[enhanced diagnostics statement]]

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

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

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

== Errors ==
* <strike>cannot redefine variable in nested block</strike>

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

=== newer 0.2.0 ===
* src from http://pgfoundry.org/frs/download.php/1325/plpgpsm-0.2.3.tgz
* extract into src/contrib/plpgpsm
* make install
* psql anydb
* \i plpgpsm.sql
* make installcheck

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)
<pre>
INSERT INTO pg_pltemplate
VALUES('plpgpsm',true,'plpgpsm_call_handler','plpgpsm_validator','$libdir/plpgpsm', NULL)
</pre>
* 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”

SQL/PSM - Historie editací

imported>WikiSysop: /* Difference from PL/pgSQL */