PL/pgSQL

PL/pgSQL
Designed byJan Wieck
DeveloperPostgreSQL Global Development Group
First appearedOctober 30, 1998; 27 years ago (1998-10-30)
Websitewww.postgresql.org/docs/current/static/plpgsql.html
Influenced by
PL/SQL, Ada

PL/pgSQL (Procedural Language/PostgreSQL) is a procedural programming language supported by the PostgreSQL ORDBMS. It closely resembles Oracle's PL/SQL language. Implemented by Jan Wieck, PL/pgSQL first appeared with PostgreSQL 6.4, released on October 30, 1998.[1] Version 9 also implements some ISO SQL/PSM features, like overloading of SQL-invoked functions and procedures.[2]

PL/pgSQL, as a fully featured programming language, allows much more procedural control than SQL, including the ability to use loops and other control structures. SQL statements and triggers can call functions created in the PL/pgSQL language.

The design of PL/pgSQL aimed to allow PostgreSQL users to perform more complex operations and computations than SQL, while providing ease of use. The language is able to be defined as trusted by the server.[3]

PL/pgSQL is one of the programming languages included in the standard PostgreSQL distribution,[4] the others being PL/Tcl, PL/Perl[5] and PL/Python.[6] In addition, many others are available from third parties, including PL/Java,[7] PL/pgPSM,[8] PL/php,[9] PL/R,[10] PL/Ruby,[11] PL/sh, PL/Lua, Postmodern[12] (based on Common Lisp) and PL/v8. PostgreSQL uses Bison as its parser,[13] making it easy to port many open-source languages, as well as to reuse code.

Comparing with PSM

The SQL/PSM language is specified by an ISO standard, but is also inspired by Oracle's PL/SQL and PL/pgPL/SQL, so there are few differences. The PL/pgPSM contributed module implements the standard. The main features of PSM that differ from PL/pgSQL:[8][14]

  • Exception handlers are subroutines (continue handlers);
  • Warnings can be handled as an exception;
  • Declaration of variables should be based on SQL query results.

All three languages (Oracle PL/SQL,[15] PostgreSQL PL/pgSQL and ISO SQL/PSM[16]) were heavily influenced by the Ada programming language.

Example

The following example is a function that computes the sales tax of a given subtotal:

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

References

  1. ^ "PostgreSQL Documentation, Appendix E: Release Notes, Release 6.4". PostgreSQL Global Development Group. January 2012.
  2. ^ "feature T322", SQL standard features (9 ed.), PostgreSQL.
  3. ^ "PL/pgSQL – SQL Procedural Language". PostgreSQL. Retrieved 2007-11-15.
  4. ^ "Procedural Languages". 9 May 2024.
  5. ^ "PL/Perl", Docs (current ed.), PostgreSQL.
  6. ^ "PL/Python", Docs (current ed.), PostgreSQL.
  7. ^ "PL/Java", Gborg (project), PostgreSQL.
  8. ^ a b SQL/PSM (manual), PostgreSQL, 20 May 2008.
  9. ^ "PL/PHP", Community, Command prompt.
  10. ^ Conway, Joe, PL/R, archived from the original on 2014-06-19, retrieved 2005-02-25.
  11. ^ PL/Ruby (project), Ruby lang, 2018-11-23.
  12. ^ Haverbeke, Marijn (2024-05-27), marijnh/Postmodern, retrieved 2024-06-01
  13. ^ "Parser stage", Docs (9 ed.), PostgreSQL
  14. ^ Stehule, P, "Proposal: PL/pgPSM for pg9.3", Hackers (mailing list), PostgreSQL.
  15. ^ "C. PL/SQL Program Limits", PL/SQL Language Reference, Database Online Documentation (11g Release 1 (11.1) ed.), Oracle, p. 87, PL/SQL is based on the programming language Ada.
  16. ^ "1", Stored procedures (ODP), O’Reilly.


Stub icon

This programming-language-related article is a stub. You can help Wikipedia by adding missing information.

Content Disclaimer

Informasi ini disarikan dari Wikipedia dan disajikan kembali untuk tujuan edukasi. Konten tersedia di bawah lisensi CC BY-SA 3.0. Kami tidak bertanggung jawab atas ketidakakuratan data yang bersumber dari kontribusi publik tersebut.

  1. The information displayed on this website is sourced in part or in whole from Wikipedia and has been adapted for the purpose of restating it. We strive to provide accurate and relevant information, however:
  2. There is no guarantee of absolute accuracy. Wikipedia is an open, collaborative project that can be edited by anyone, so information is subject to change.
  3. It is not intended to constitute professional advice. The content displayed is for informational and educational purposes only. For important decisions (e.g., medical, legal, or financial), please consult a professional.
  4. Content copyright. Wikipedia is licensed under the Creative Commons Attribution-ShareAlike License (CC BY-SA). This means that content may be reused with appropriate attribution and shared under a similar license.
  5. Responsible use. Any risk arising from the use of information from this website is entirely the responsibility of the user.