SQL/PSM

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[2]), 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:2016. 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.[3] 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.

IBM's SQL PL (used in DB2) and Mimer SQL's PSM[4] were the first two products implementing SQL/PSM. In practice those two, and perhaps also MySQL/MariaDB's procedural language, are closest to the SQL/PSM standard. [5][6]

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[7] are now supported. A PostgreSQL addon implements SQL/PSM[8][9][10][11] (alongside its own procedural language), although it is not part of the core product.[12]

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).

See also

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

Open source:

Proprietary:

References

  1. ^ Gulutzan, Peter (2015), Stored Procedures: critiques and defences 
  2. ^ Eisenberg, A. (1996). "New standard for stored procedures in SQL". ACM SIGMOD Record. 25 (4): 81-88. doi:10.1145/245882.245907. 
  3. ^ Melton, Jim; Simon, Alan R (2002). SQL: 1999. Morgan Kaufmann. pp. 541-42. ISBN 978-1-55860-456-8. 
  4. ^ https://developer.mimer.com/documentation/sql/stored-procedures-in-mimer-sql/
  5. ^ a b Harrison, Guy; Feuerstein, Steven (2008). MySQL Stored Procedure Programming. O'Reilly. p. 49. ISBN 978-0-596-10089-6. 
  6. ^ a b https://mariadb.com/kb/en/library/stored-procedures/
  7. ^ "feature T322", SQL standard features (9 ed.), PostgreSQL .
  8. ^ plpsm0 (git) (repository) .
  9. ^ Announce, PostgreSQL, May 2011 .
  10. ^ 2012-2's Proposal PL/pgPSM announce
  11. ^ SQL/PSM (wiki) (manual), PostgreSQL, 2008 .
  12. ^ "SQL Conformance", Documentation (9.2 ed.), PostgreSQL .
  13. ^ http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#src_psm_routines
  14. ^ http://docs.openlinksw.com/virtuoso/ch-sqlprocedures/
  15. ^ Invantive Procedural SQL grammar

Further reading

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



  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.

SQL/PSM
 



 

Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.


Manage research, learning and skills at defaultLogic. Create an account using LinkedIn or facebook to manage and organize your Digital Marketing and Technology knowledge. defaultLogic works like a shopping cart for information -- helping you to save, discuss and share.

Visit defaultLogic's partner sites below:
PopFlock.com : Music Genres | Musicians | Musical Instruments | Music Industry
NCR Works : Retail Banking | Restaurant Industry | Retail Industry | Hospitality Industry

  Contact Us