SQL/PSM

SQL/PSM (<a href="/facts/SQL/UjQLJcYm">SQL</a>/Persistent Stored Modules) is an <a href="/facts/ISO_standard/IacYR7Yg">ISO standard</a> mainly defining an extension of SQL with a <a href="/facts/Procedural_language/7r5xz7kx">procedural language</a> for use in <a href="/facts/Stored_procedure/50xRehaI">stored procedures</a>. Initially published in 1996 as an extension of <a href="/facts/SQL-92/a9th5tCf">SQL-92</a> (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 <a href="/facts/SQL%3a1999/lNJB4sgR">SQL:1999</a> standard, and has been part 4 of that standard since then, most recently in <a href="/facts/SQL%3a2023/z4NTEYrc">SQL:2023</a>. 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 <a href="/facts/Control_flow/2XTDfErC">control flow</a>, <a href="/facts/Exception_handling/VrJxgb9r">exception handling</a> (called "condition handling" in SQL/PSM), local variables, assignment of expressions to variables and parameters, and (procedural) use of <a href="/facts/Cursor_(databases)/4PSbd1CC">cursors</a>. It also defines an information schema (<a href="/facts/Metadata/qt35z79C">metadata</a>) for stored procedures. SQL/PSM is one language in which <a href="/facts/Method_(computer_programming)/8kAXxVYs">methods</a> for the SQL:1999 <a href="/facts/Structured_type/T1H2SpIN">structured types</a> can be defined. The other is Java, via <a href="/facts/SQL%2fJRT/u0Y6eJe3">SQL/JRT</a>.
SQL/PSM is derived, seemingly directly, from Oracle's <a href="/facts/PL%2fSQL/LLghPhdc">PL/SQL</a>. Oracle developed PL/SQL and released it in 1991, basing the language on the <a href="/facts/United_States_Department_of_Defense/dWAvf7e3">US Department of Defense</a>'s <a href="/facts/Ada_(programming_language)/Y2yHel4l">Ada programming language</a>. However, Oracle has maintained a distance from the standard in its documentation. IBM's <a href="/facts/SQL_PL/x6aPMLN9">SQL PL</a> (used in DB2) and <a href="/facts/Mimer_SQL/88K5koLW">Mimer SQL</a>'s PSM were the first two products officially implementing SQL/PSM. It is commonly thought that these two languages, and perhaps also <a href="/facts/MySQL/QEEDNBDP">MySQL</a>/<a href="/facts/MariaDB/QWobVFIq">MariaDB</a>'s procedural language, are closest to the SQL/PSM standard.

However, a <a href="/facts/PostgreSQL/LHdXuHfl">PostgreSQL</a> addon implements SQL/PSM (alongside its other procedural languages like the PL/SQL-derived plpgsql), although it is not part of the core product.
<a href="/facts/Resource_Description_Framework/WMufyukm">RDF</a> functionality in <a href="/facts/Virtuoso_Universal_Server/QdalvPFw">OpenLink Virtuoso</a> 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).

SQL/PSM open-in-new

SQL/PSM