PL/pgSQL

From Wikipedia, the free encyclopedia
Jump to: navigation, search
PL/pgSQL
Postgresql elephant.svg
Designed by Jan Wieck
Developer PostgreSQL Global Development Group
First appeared October 30, 1998; 16 years ago (1998-10-30)
Website www.postgresql.org/docs/current/static/plpgsql.html

PL/pgSQL (Procedural Language/PostgreSQL) is a procedural programming language supported by the PostgreSQL ORDBMS. It closely resembles Oracle's PL/SQL language. PL/pgSQL was implemented by Jan Wieck and first appeared with PostgreSQL 6.4, released on October 30, 1998.[1] The current version 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. Functions created in the PL/pgSQL language can be called from an SQL statement, or as the action that a trigger performs.

PL/pgSQL was created to be able to perform more complex operations and computations than SQL, while being easy to use, and is able to be defined as trusted by the server.[3]

PL/pgSQL is the only programming language installed by default for PostgreSQL, but many others are available, including PL/Java,[4] PL/Perl,[5] PL/pgPSM,[6] PL/php,[7] PL/Python,[8] PL/R,[9] PL/Ruby,[10] PL/sh, PL/Tcl, and PL/Lua. PostgreSQL uses Bison as its parser,[11] so it is easy to port many open source languages, as well as reuse code.

Comparing with PSM[edit]

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:[6][12]

  • 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,[13] PostgreSQL PL/pgSQL and ISO SQL/PSM[14]) are originally descended from the Ada programming language.

References[edit]

  1. ^ "PostgreSQL Documentation, Appendix E: Release Notes, Release 6.4". PostgreSQL Global Development Group. 
  2. ^ "feature T322", SQL standard features (9 ed.), PostgreSQL .
  3. ^ "PL/pgSQL – SQL Procedural Language". PostgreSQL. Retrieved 2007-11-15. 
  4. ^ "PL/Java", Gborg (project), PostgreSQL .
  5. ^ "PL/Perl", Docs (current ed.), PostgreSQL .
  6. ^ a b SQL/PSM (manual), PostgreSQL .
  7. ^ "PL/PHP", Community, Command prompt .
  8. ^ "PL/Python", Docs (current ed.), PostgreSQL .
  9. ^ Conway, Joe, PL/R .
  10. ^ PL/Ruby (project), Ruby lang .
  11. ^ "Parser stage", Docs (9 ed.), PostgreSQL .
  12. ^ Stehule, P, "Proposal: PL/pgPSM for pg9.3", Hackers (mailing list), PostgreSQL .
  13. ^ "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. 
  14. ^ "1", Stored procedures (ODP), O’Reilly .

External links[edit]