| SQL/PSM | |
|---|---|
| Paradigm | Multi-paradigm |
| First appeared | 1996 |
| Stable release | |
| OS | Cross-platform (multi-platform) |
| Majorimplementations | |
| PL/SQL MySQL/MariaDB IBM'sSQL PL Mimer SQL | |
| Influenced by | |
| PL/SQL Ada[1] | |
SQL/PSM (SQL/Persistent Stored Modules) is anISO standard mainly defining an extension of SQL with aprocedural language for use instored procedures. Initially published in 1996 as an extension ofSQL-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-partSQL:1999 standard, and has been part 4 of that standard since then, most recently inSQL:2023.[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 forcontrol flow,exception handling (called "condition handling" in SQL/PSM), local variables, assignment of expressions to variables and parameters, and (procedural) use ofcursors. It also defines an information schema (metadata) for stored procedures. SQL/PSM is one language in whichmethods for the SQL:1999structured types can be defined. The other is Java, viaSQL/JRT.
SQL/PSM is derived, seemingly directly, from Oracle'sPL/SQL. Oracle developed PL/SQL and released it in 1991, basing the language on theUS Department of Defense'sAda programming language. However, Oracle has maintained a distance from the standard in its documentation. IBM'sSQL PL (used in DB2) andMimer SQL's PSM[5] were the first two products officially implementing SQL/PSM. It is commonly thought that these two languages, and perhaps alsoMySQL/MariaDB's procedural language, are closest to the SQL/PSM standard.[6][7] However, aPostgreSQL 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 functionality inOpenLink 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).
The following implementations adopt the standard, but they are not 100% compatible to SQL/PSM:
Proprietary: