# SQL/PSM > Mediated Wiki article. Canonical URL: https://mediated.wiki/source/SQL/PSM > Markdown URL: https://mediated.wiki/source/SQL/PSM.md > Source: https://en.wikipedia.org/wiki/SQL%2FPSM > Source revision: 1357199956 > License: Creative Commons Attribution-ShareAlike 4.0 International (https://creativecommons.org/licenses/by-sa/4.0/) **SQL/PSM** ([SQL](/source/SQL)/Persistent Stored Modules) is an [ISO standard](/source/ISO_standard) mainly defining an extension of SQL with a [procedural language](/source/Procedural_language) for use in [stored procedures](/source/Stored_procedure). Initially published in 1996 as an extension of [SQL-92](/source/SQL-92) (ISO/IEC 9075-4:1996, a version sometimes called PSM-96 or even SQL-92/PSM[2]), SQL/PSM was later incorporated into the multi-part standard, and has been part 4 of that standard since then, most recently in .[3] 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.[4] 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](/source/Control_flow), [exception handling](/source/Exception_handling) (called "condition handling" in SQL/PSM), local variables, assignment of expressions to variables and parameters, and (procedural) use of [cursors](/source/Cursor_(databases)). It also defines an information schema ([metadata](/source/Metadata)) for stored procedures. SQL/PSM is one language in which [methods](/source/Method_(computer_programming)) for the SQL:1999 [structured types](/source/Structured_type) can be defined. The other is Java, via [SQL/JRT](/source/SQL/JRT). SQL/PSM is derived, seemingly directly, from Oracle's [PL/SQL](/source/PL/SQL). Oracle developed PL/SQL and released it in 1991, basing the language on the [US Department of Defense](/source/United_States_Department_of_Defense)'s [Ada programming language](/source/Ada_(programming_language)). However, Oracle has maintained a distance from the standard in its documentation. IBM's [SQL PL](/source/SQL_PL) (used in DB2) and [Mimer SQL](/source/Mimer_SQL)'s PSM[5] were the first two products officially implementing SQL/PSM. It is commonly thought that these two languages, and perhaps also [MySQL](/source/MySQL)/[MariaDB](/source/MariaDB)'s procedural language, are closest to the SQL/PSM standard. [6] [7] However, a [PostgreSQL](/source/PostgreSQL) addon implements SQL/PSM[8][9][10][11] (alongside its other procedural languages like the PL/SQL-derived plpgsql), although it is not part of the core product.[12] [RDF](/source/Resource_Description_Framework) functionality in [OpenLink Virtuoso](/source/Virtuoso_Universal_Server) 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). ## See also The following implementations adopt the standard, but they are not 100% compatible to SQL/PSM: [Open source](/source/Open_source): - [HSQLDB](/source/HSQLDB) stored procedures and functions[13] - [MySQL](/source/MySQL) stored procedures[6] - [MariaDB](/source/MariaDB) stored procedures[7] - [OpenLink Virtuoso](/source/Virtuoso_Universal_Server) SQL Procedures (VSP)[14] - [PostgreSQL](/source/PostgreSQL) [PL/pgSQL](/source/PL/pgSQL) Proprietary: - [Oracle](/source/Oracle_Database) [PL/SQL](/source/PL/SQL) - [Microsoft](/source/Microsoft_SQL_Server) and [Sybase](/source/Sybase_ASE) [Transact-SQL](/source/Transact-SQL) - Invantive Procedural SQL[15] - [Mimer SQL](/source/Mimer_SQL) stored procedures [16] ## References 1. Gulutzan, Peter (2015), ["Stored Procedures: critiques and defences"](https://ocelot.ca/blog/blog/2015/01/15/stored-procedures-critiques-and-defences/) 1. Eisenberg, A. (1996). "New standard for stored procedures in SQL". *ACM SIGMOD Record*. **25** (4): 81–88. [doi:10.1145/245882.245907](https://doi.org/10.1145/245882.245907). [S2CID 13023149](https://api.semanticscholar.org/CorpusID:13023149) 1. ["SQL:2023"](https://www.iso.org/standard/76585.html), "Catalogue" (webshop), ISO 1. Melton, Jim & Simon, Alan R (2002). [*SQL: 1999*](https://archive.org/details/sql1999understan0000melt/page/541). Morgan Kaufmann. pp. [541–42](https://archive.org/details/sql1999understan0000melt/page/541). ISBN 978-1-55860-456-8. 1. ["Stored Procedures in Mimer SQL"](https://developer.mimer.com/article/stored-procedures-in-mimer-sql/) 1. Harrison, Guy & Feuerstein, Steven (2008). [*MySQL Stored Procedure Programming*](https://books.google.com/books?id=YpeP0ok0cO4C&pg=PT75). O'Reilly. p. 49. ISBN 978-0-596-10089-6. 1. ["Stored Procedures"](https://mariadb.com/kb/en/library/stored-procedures/). *MariaDB KnowledgeBase* 1. ["plpsm0"](https://github.com/okbob/plpsm0) (repository) (git), 7 July 2020. 1. ["Announce"](https://www.postgresql.org/message-id/1305291347.14548.13.camel@jara.office.nic.cz), PostgreSQL, May 2011. 1. ["PostgreSQL: Proposal: PL/pgPSM for 9.3"](https://www.postgresql.org/message-id/CAFj8pRDWFdcjNSnwQB_3j1-rMO6b8=TmLTNBvDCSpRrOW2Dfeg@mail.gmail.com). *www.postgresql.org*. 22 February 2012. 1. ["SQL/PSM"](https://postgres.cz/wiki/SQL/PSM_Manual) (manual) (wigl), PostgreSQL, 2008. 1. ["SQL Conformance"](https://www.postgresql.org/docs/9.2/static/features.html), "Documentation", 9.2 ed., PostgreSQL. 1. ["Chapter 8. SQL-Invoked Routines"](https://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#src_psm_routines). *hsqldb.org* 1. ["Chapter 11. SQL Procedure Language Guide"](https://docs.openlinksw.com/virtuoso/ch-sqlprocedures/). *docs.openlinksw.com* 1. ["Invantive SQL v2.0 Grammar"](https://documentation.invantive.com/2017R2/invantive-sql-grammar/invantive-sql-grammar-17.30.html#pSqlBlock). *Invantive* 1. ["Mimer SQL Stored Procedures"](https://docs.mimer.com/MimerSqlManual/latest/Manuals/PSM/PSM.htm). *docs.mimer.com* ## Further reading - Jim Melton, *Understanding SQL's Stored Procedures: A Complete Guide to SQL/PSM*, Morgan Kaufmann Publishers, 1998, ISBN 1-55860-461-8 --- Adapted from the Wikipedia article [SQL/PSM](https://en.wikipedia.org/wiki/SQL%2FPSM) by Wikipedia contributors ([contributor history](https://en.wikipedia.org/wiki/SQL%2FPSM?action=history)). Available under [Creative Commons Attribution-ShareAlike 4.0 International](https://creativecommons.org/licenses/by-sa/4.0/). Changes may have been made.