Menu
Home Explore People Places Arts History Plants & Animals Science Life & Culture Technology
On this page
SQL/PSM
ISO standard mainly defining an extension of SQL with a procedural language for use in stored procedures

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:2023. 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 is derived, seemingly directly, from Oracle's PL/SQL. Oracle developed PL/SQL and released it in 1991, basing the language on the US Department of Defense's Ada programming language. However, Oracle has maintained a distance from the standard in its documentation. IBM's SQL PL (used in DB2) and Mimer SQL's PSM were the first two products officially implementing SQL/PSM. It is commonly thought that these two languages, and perhaps also MySQL/MariaDB's procedural language, are closest to the SQL/PSM standard. However, a PostgreSQL addon implements SQL/PSM (alongside its other procedural languages like the PL/SQL-derived plpgsql), although it is not part of the core product.

RDF functionality in OpenLink Virtuoso was developed entirely through SQL/PSM, combined with custom datatypes (e.g., ANY for handling URI and Literal relation objects), sophisticated indexing, and flexible physical storage choices (column-wise or row-wise).

We don't have any images related to SQL/PSM yet.
We don't have any YouTube videos related to SQL/PSM yet.
We don't have any PDF documents related to SQL/PSM yet.
We don't have any Books related to SQL/PSM yet.
We don't have any archived web articles related to SQL/PSM yet.

See also

The following implementations adopt the standard, but they are not 100% compatible to SQL/PSM:

Open source:

Proprietary:

Further reading

  • Jim Melton, Understanding SQL's Stored Procedures: A Complete Guide to SQL/PSM, Morgan Kaufmann Publishers, 1998, ISBN 1-55860-461-8

References

  1. Eisenberg, A. (1996). "New standard for stored procedures in SQL". ACM SIGMOD Record. 25 (4): 81–88. doi:10.1145/245882.245907. S2CID 13023149. https://doi.org/10.1145%2F245882.245907

  2. "SQL:2023", Catalogue (webshop), ISO https://www.iso.org/standard/76585.html

  3. Melton, Jim; Simon, Alan R (2002). SQL: 1999. Morgan Kaufmann. pp. 541–42. ISBN 978-1-55860-456-8. 978-1-55860-456-8

  4. "Stored Procedures in Mimer SQL". https://developer.mimer.com/article/stored-procedures-in-mimer-sql/

  5. Harrison, Guy; Feuerstein, Steven (2008). MySQL Stored Procedure Programming. O'Reilly. p. 49. ISBN 978-0-596-10089-6. 978-0-596-10089-6

  6. "Stored Procedures". MariaDB KnowledgeBase. https://mariadb.com/kb/en/library/stored-procedures/

  7. plpsm0 (git) (repository), 7 July 2020. https://github.com/okbob/plpsm0

  8. Announce, PostgreSQL, May 2011. http://www.postgresql.org/message-id/[email protected]

  9. "PostgreSQL: Proposal: PL/pgPSM for 9.3". www.postgresql.org. 22 February 2012. https://www.postgresql.org/message-id/CAFj8pRDWFdcjNSnwQB_3j1-rMO6b8=TmLTNBvDCSpRrOW2Dfeg@mail.gmail.com

  10. SQL/PSM (wigl) (manual), PostgreSQL, 2008. http://postgres.cz/wiki/SQL/PSM_Manual

  11. "SQL Conformance", Documentation (9.2 ed.), PostgreSQL. http://www.postgresql.org/docs/9.2/static/features.html

  12. "Chapter 8. SQL-Invoked Routines". hsqldb.org. http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#src_psm_routines

  13. Harrison, Guy; Feuerstein, Steven (2008). MySQL Stored Procedure Programming. O'Reilly. p. 49. ISBN 978-0-596-10089-6. 978-0-596-10089-6

  14. "Stored Procedures". MariaDB KnowledgeBase. https://mariadb.com/kb/en/library/stored-procedures/

  15. "Chapter 11. SQL Procedure Language Guide". docs.openlinksw.com. http://docs.openlinksw.com/virtuoso/ch-sqlprocedures/

  16. "Invantive SQL v2.0 Grammar". Invantive. https://documentation.invantive.com/2017R2/invantive-sql-grammar/invantive-sql-grammar-17.30.html#pSqlBlock

  17. "Mimer SQL Stored Procedures". docs.mimer.com. https://docs.mimer.com/MimerSqlManual/latest/Manuals/PSM/PSM.htm