View source for PL/pgSQL

{{Infobox programming language
| name = PL/pgSQL
| title = 
| logo = [[File:Postgresql elephant.svg|120px]]
| logo caption = 
| screenshot =         <!-- (filename) -->
| screenshot caption = 
| paradigm =           <!-- or: | paradigms = -->
| family =  
| designer = Jan Wieck
| developer = PostgreSQL Global Development Group
| released = {{start date and age|1998|10|30|df=no}}
| latest release version = 
| latest release date =     <!-- {{start date and age|YYYY|MM|DD|df=yes/no}} -->
| latest preview version = 
| latest preview date =     <!-- {{start date and age|YYYY|MM|DD|df=yes/no}} -->
| typing = 
| scope = 
| programming language = 
| platform = 
| operating system = 
| license = 
| file ext = 
| file format =        <!-- or: | file formats = -->
| website = {{url|https://www.postgresql.org/docs/current/static/plpgsql.html}}
| implementations = 
| dialects = 
| influenced by = [[PL/SQL]], [[Ada (programming language)|Ada]]
| influenced = 
}}
'''PL/pgSQL''' ('''Procedural Language/PostgreSQL''') is a [[procedural programming|procedural]] [[programming language]] supported by the [[PostgreSQL]] [[ORDBMS]].  It closely resembles [[Oracle Database|Oracle]]'s [[PL/SQL]] language. Implemented by Jan Wieck, PL/pgSQL first appeared with PostgreSQL 6.4, released on October 30, 1998.<ref>{{cite web |url= https://www.postgresql.org/docs/current/static/release-6-4.html |title= PostgreSQL Documentation, Appendix E: Release Notes, Release 6.4 |date= January 2012 |publisher= PostgreSQL Global Development Group}}</ref> Version 9 also implements some ISO [[SQL/PSM]] features, like overloading of SQL-invoked functions and procedures.<ref>{{Citation | publisher = PostgreSQL | title = SQL standard features | edition = 9 | contribution-url = https://www.postgresql.org/docs/9.0/static/features-sql-standard.html | contribution = feature T322}}.</ref>

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 [[database trigger|trigger]]s 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.<ref>{{cite web | url = https://www.postgresql.org/docs/current/static/plpgsql.html | title = PL/pgSQL – SQL Procedural Language | publisher = PostgreSQL | accessdate = 2007-11-15}}</ref>

PL/pgSQL is one of the programming languages included in the standard PostgreSQL distribution,<ref>{{cite web|title=Procedural Languages|date=9 May 2024 |url=https://www.postgresql.org/docs/current/static/xplang.html}}</ref> the others being [https://www.postgresql.org/docs/current/static/pltcl.html PL/Tcl], PL/Perl<ref>{{Citation | title = Docs | contribution = PL/Perl | contribution-url = https://www.postgresql.org/docs/current/static/plperl.html | edition = current | publisher = PostgreSQL}}.</ref> and PL/Python.<ref>{{Citation | title = Docs | contribution = PL/Python | contribution-url = https://www.postgresql.org/docs/current/static/plpython.html | edition = current | publisher = PostgreSQL}}.</ref> In addition, many others are available from third parties, including PL/Java,<ref>{{Citation | contribution = PL/Java | title = Gborg | type = project | contribution-url = https://tada.github.io/pljava/ | publisher = PostgreSQL}}.</ref> PL/pgPSM,<ref name = PSM>{{Citation | title = SQL/PSM | date = 20 May 2008 | url = https://postgres.cz/wiki/SQL/PSM_Manual | publisher = PostgreSQL | type = manual}}.</ref> PL/php,<ref>{{Citation | contribution = PL/PHP | contribution-url = https://public.commandprompt.com/projects/plphp/ | publisher = Command prompt | title = Community}}.</ref> PL/R,<ref>{{Citation | last = Conway | first = Joe | title = PL/R | url = http://www.joeconway.com/plr/ | access-date = 2005-02-25 | archive-url = https://web.archive.org/web/20140619175406/http://www.joeconway.com/plr/ | archive-date = 2014-06-19 | url-status = dead }}.</ref>  PL/Ruby,<ref>{{Citation | title = PL/Ruby | url = https://github.com/knu/postgresql-plruby | type = project | publisher = Ruby lang| date = 2018-11-23 }}.</ref> 
[https://github.com/petere/plsh PL/sh], 
[https://github.com/pllua/pllua  PL/Lua], Postmodern<ref>{{Citation |last=Haverbeke |first=Marijn |title=marijnh/Postmodern |date=2024-05-27 |url=https://github.com/marijnh/Postmodern |access-date=2024-06-01}}</ref> (based on [[Common Lisp]]) and [https://pgxn.org/dist/plv8/doc/plv8.html PL/v8].  PostgreSQL uses [[GNU bison|Bison]] as its parser,<ref>
{{Citation | title = Docs | edition = 9 
| contribution = Parser stage 
| publisher = PostgreSQL 
| contribution-url = https://www.postgresql.org/docs/9.0/static/parser-stage.html
}}
</ref>
making it easy to port many [[open-source model|open-source]] languages, as well as to [[Code reuse|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:<ref name = PSM /><ref name = proposal>{{Citation | title=Hackers | type = mailing list | publisher = PostgreSQL | contribution-url = https://www.postgresql.org/message-id/CAFj8pRDWFdcjNSnwQB_3j1-rMO6b8=TmLTNBvDCSpRrOW2Dfeg@mail.gmail.com | contribution = Proposal: PL/pgPSM for pg9.3 | first = P | last = Stehule}}.</ref>

* 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,<ref>{{Citation | series = Database Online Documentation | edition = 11g Release 1 (11.1) | title = PL/SQL Language Reference | page = 87 | chapter = C. PL/SQL Program Limits | publisher = Oracle | chapter-url = http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/limits.htm#LNPLS018 | quote = PL/SQL is based on the programming language Ada.}}</ref> PostgreSQL PL/pgSQL and ISO SQL/PSM<ref>{{Citation | title = Stored procedures | chapter = 1 | publisher = O’Reilly | format = [[Open document presentation|ODP]] | url = http://cdn.oreillystatic.com/en/assets/1/event/36/Stored%2520Procedures%2520-%2520Part%25201%2520Presentation.odp}}.</ref>) were heavily influenced by the [[Ada (programming language)|Ada programming language]].

== Example ==
The following example is a function that computes the sales tax of a given subtotal:<syntaxhighlight lang="plpgsql">
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
</syntaxhighlight>

== Inline documentation external support ==
{{Unsourced|section|date=March 2016}}
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.
  
{|class="wikitable" 
|-
!Tool
!Full PL/pgSQL
![[Javadoc]] style
!Other style
!PL/pgSQL projects using it
|-
|Document! X ||{{dunno}} || {{yes}} || {{yes}} || {{dunno}}
|-
|[[Natural Docs]] ||{{dunno}} || {{no}}  || {{yes}} || {{dunno}}
|-
|[[ROBODoc]] ||{{dunno}} || {{yes}}  || {{no}} || {{dunno}}
|}

Other documentation tools: [[Doxygen]], DBScribe, HyperSQL, Universal Report.

==References==
{{reflist}}

==External links==
*[https://www.postgresql.org/docs/current/static/plpgsql.html Official PL/pgSQL documentation]

{{DEFAULTSORT:PL pgSQL}}
[[Category:SQL]]
[[Category:PostgreSQL]]
[[Category:Data-centric programming languages]]


{{compu-lang-stub}}