PL/pgSQL (Procedural Language/PostgreSQL) is a procedural programming language supported by the PostgreSQL ORDBMS, closely resembling Oracle's PL/SQL. Introduced in PostgreSQL 6.4 (1998), it offers greater procedural control than standard SQL, enabling loops, control structures, and calls from triggers. It also supports some ISO SQL/PSM features. PL/pgSQL is included in the core distribution alongside other languages like PL/Tcl, PL/Perl, and PL/Python, with many third-party languages available, such as PL/sh, PL/Lua, and PL/v8. PostgreSQL uses Bison as its parser, facilitating code reuse and open-source language portability.
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:1415
- 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,16 PostgreSQL PL/pgSQL and ISO SQL/PSM17) 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;Inline documentation external support
Formal pseudo-language for documentation can be embedded in SQL and PL/pgSQL scripts. This documentation is then processed by a documentation generator—an external tool which extracts data and generates hypertext. Since PL/SQL: supports some of these tools, PL/pgSQL is expected to provide full or partial support as well.
| Tool | Full PL/pgSQL | Javadoc style | Other style | PL/pgSQL projects using it |
|---|---|---|---|---|
| Document! X | ? | Yes | Yes | ? |
| Natural Docs | ? | No | Yes | ? |
| ROBODoc | ? | Yes | No | ? |
Other documentation tools: Doxygen, DBScribe, HyperSQL, Universal Report.
External links
References
"PostgreSQL Documentation, Appendix E: Release Notes, Release 6.4". PostgreSQL Global Development Group. January 2012. https://www.postgresql.org/docs/current/static/release-6-4.html ↩
"feature T322", SQL standard features (9 ed.), PostgreSQL. https://www.postgresql.org/docs/9.0/static/features-sql-standard.html ↩
"PL/pgSQL – SQL Procedural Language". PostgreSQL. Retrieved 2007-11-15. https://www.postgresql.org/docs/current/static/plpgsql.html ↩
"Procedural Languages". 9 May 2024. https://www.postgresql.org/docs/current/static/xplang.html ↩
"PL/Perl", Docs (current ed.), PostgreSQL. https://www.postgresql.org/docs/current/static/plperl.html ↩
"PL/Python", Docs (current ed.), PostgreSQL. https://www.postgresql.org/docs/current/static/plpython.html ↩
"PL/Java", Gborg (project), PostgreSQL. https://tada.github.io/pljava/ ↩
SQL/PSM (manual), PostgreSQL, 20 May 2008. https://postgres.cz/wiki/SQL/PSM_Manual ↩
"PL/PHP", Community, Command prompt. https://public.commandprompt.com/projects/plphp/ ↩
Conway, Joe, PL/R, archived from the original on 2014-06-19, retrieved 2005-02-25. https://web.archive.org/web/20140619175406/http://www.joeconway.com/plr/ ↩
PL/Ruby (project), Ruby lang, 2018-11-23. https://github.com/knu/postgresql-plruby ↩
Haverbeke, Marijn (2024-05-27), marijnh/Postmodern, retrieved 2024-06-01 https://github.com/marijnh/Postmodern ↩
"Parser stage", Docs (9 ed.), PostgreSQL https://www.postgresql.org/docs/9.0/static/parser-stage.html ↩
SQL/PSM (manual), PostgreSQL, 20 May 2008. https://postgres.cz/wiki/SQL/PSM_Manual ↩
Stehule, P, "Proposal: PL/pgPSM for pg9.3", Hackers (mailing list), PostgreSQL. https://www.postgresql.org/message-id/CAFj8pRDWFdcjNSnwQB_3j1-rMO6b8=TmLTNBvDCSpRrOW2Dfeg@mail.gmail.com ↩
"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. http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/limits.htm#LNPLS018 ↩
"1", Stored procedures (ODP), O’Reilly. http://cdn.oreillystatic.com/en/assets/1/event/36/Stored%2520Procedures%2520-%2520Part%25201%2520Presentation.odp ↩