SQL/PSM (SQL/Persistent Stored Modules) is an ISO standard mainly defining an extension of SQL with a procedural language for use in stored procedures. Initially published in 1996 as an extension of SQL-92 (ISO/IEC 9075-4:1996, a version sometimes called PSM-96 or even SQL-92/PSM), SQL/PSM was later incorporated into the multi-part SQL:1999 standard, and has been part 4 of that standard since then, most recently in SQL:2011. The SQL:1999 part 4 covered less than the original PSM-96 because the SQL statements for defining, managing, and invoking routines were actually incorporated into part 2 SQL/Foundation, leaving only the procedural language itself as SQL/PSM. The SQL/PSM facilities are still optional as far as the SQL standard is concerned; most of them are grouped in Features P001-P008.
SQL/PSM standardizes syntax and semantics for control flow, exception handling (called "condition handling" in SQL/PSM), local variables, assignment of expressions to variables and parameters, and (procedural) use of cursors. It also defines an information schema (metadata) for stored procedures. SQL/PSM is one language in which methods for the SQL:1999 structured types can be defined. The other is Java, via SQL/JRT.
SQL/PSM resembles and inspired by PL/SQL, as well as PL/pgSQL, so they are similar languages. With PostgreSQL v9 some SQL/PSM features, like overloading of SQL-invoked functions and procedures are now supported. A PostgreSQL addon implements SQL/PSM (alongside its own procedural language), although it is not part of the core product.
Open source similar languages:
- MySQL stored procedures (a procedural language that closely adheres to SQL/PSM)
- PL/pgSQL (PostgreSQL language similar to SQL/PSM and PL/SQL)
Proprietary similar languages:
- PL/SQL (Oracle proprietary language for stored procedures)
- Transact-SQL (Microsoft and Sybase equivalent)
- Eisenberg, A. (1996). "New standard for stored procedures in SQL". ACM SIGMOD Record 25 (4): 81–88. doi:10.1145/245882.245907.
- Melton, Jim; Simon, Alan R (2002). SQL: 1999. Morgan Kaufmann. pp. 541–42. ISBN 978-1-55860-456-8.
- Harrison, Guy; Feuerstein, Steven (2008). MySQL Stored Procedure Programming. O'Reilly. p. 49. ISBN 978-0-596-10089-6.
- "feature T322", SQL standard features (9 ed.), PostgreSQL.
- plpsm0 (git) (repository).
- Announce, PostgreSQL, May 2011.
- 2012-2's Proposal PL/pgPSM announce
- SQL/PSM (wiki) (manual), PostgreSQL, 2008.
- "SQL Conformance", Documentation (9.2 ed.), PostgreSQL.
- Jim Melton, Understanding SQL's Stored Procedures: A Complete Guide to SQL/PSM, Morgan Kaufmann Publishers, 1998, ISBN 1-55860-461-8
|This programming language–related article is a stub. You can help Wikipedia by expanding it.|
|This database-related article is a stub. You can help Wikipedia by expanding it.|