PL/pgSQL (Procedural Language/PostgreSQL) is a
procedural programming language
A programming language is a system of notation for writing computer programs. Most programming languages are text-based formal languages, but they may also be graphical. They are a kind of computer language.
The description of a programming ...
supported by the
PostgreSQL ORDBMS. It closely resembles
Oracle's
PL/SQL
PL/SQL (Procedural Language for SQL) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 6 - stored PL/SQL procedures/functions/packages/triggers since ...
language. Implemented by Jan Wieck, PL/pgSQL first appeared with PostgreSQL 6.4, released on October 30, 1998. Version 9 also implements some ISO
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 sometim ...
features, like overloading of SQL-invoked functions and procedures.
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
trigger
Trigger may refer to:
Notable animals and people
;Mononym
* Trigger (horse), owned by cowboy star Roy Rogers
;Nickname
* Trigger Alpert (1916–2013), American jazz bassist
* "Trigger Mike" Coppola (1900–1966), American gangster
;Surname
* Bru ...
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.
PL/pgSQL is one of the programming languages included in the standard PostgreSQL distribution, the others bein
PL/Tcl PL/Perl and PL/Python. In addition, many others are available from third parties, including PL/Java, PL/pgPSM,
[.] PL/php, PL/R, PL/Ruby,
PL/sh PL/Luaan
PostgreSQL uses
Bison as its parser,
making it easy to port many
open-source languages, as well as to
reuse code.
Comparing with PSM
The
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 sometim ...
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:
[.]
* Exception handlers are subroutines (continue handlers);
* Warnings can be handled like an exception;
* Declaration of variables should be based on SQL query result.
All three languages (Oracle PL/SQL, PostgreSQL PL/pgSQL and ISO SQL/PSM
[.]) are originally descended from the
Ada programming language
Ada is a structured, statically typed, imperative, and object-oriented high-level programming language, extended from Pascal and other languages. It has built-in language support for '' design by contract'' (DbC), extremely strong typing, expl ...
.
Example
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
A documentation generator is a programming tool that generates software documentation intended for programmers ( API documentation) or end users (end-user guide), or both, from a set of source code files, and in some cases, binary files. Some gen ...
—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.
Other documentation tools:
Doxygen
Doxygen ( ) is a documentation generator and static analysis tool for software source trees. When used as a documentation generator, Doxygen extracts information from specially-formatted comments within the code. When used for analysis, Doxyge ...
, DBScribe, HyperSQL, Universal Report.
References
External links
Official PL/pgSQL documentationPL/pgSQL (en)
tutorial and examples
SQL
PostgreSQL
Data-centric programming languages
{{compu-lang-stub