PL/pgSQL

Last updated
PL/pgSQL
Postgresql elephant.svg
Designed by Jan Wieck
Developer PostgreSQL Global Development Group
First appearedOctober 30, 1998;25 years ago (1998-10-30)
Website www.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]

Contents

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]

All three languages (Oracle PL/SQL, [15] PostgreSQL PL/pgSQL and ISO SQL/PSM [16] ) are originally descended from the Ada programming language.

Example

CREATEFUNCTIONsales_tax(subtotalreal)RETURNSrealAS$$BEGINRETURNsubtotal*0.06;END;$$LANGUAGEplpgsql;

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.

ToolFull PL/pgSQL Javadoc styleOther stylePL/pgSQL projects using it
Document! X ?YesYes ?
Natural Docs  ?NoYes ?
ROBODoc  ?YesNo ?

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

Related Research Articles

An integrated development environment (IDE) is a software application that provides comprehensive facilities for software development. An IDE normally consists of at least a source-code editor, build automation tools, and a debugger. Some IDEs, such as IntelliJ IDEA, Eclipse and Lazarus contain the necessary compiler, interpreter or both; others, such as SharpDevelop and NetBeans, do not.

<span class="mw-page-title-main">PostgreSQL</span> Free and open-source object relational database management system

PostgreSQL also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL features transactions with atomicity, consistency, isolation, durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures. It is supported on all major operating systems, including Linux, FreeBSD, OpenBSD, macOS, and Windows, and handles a range of workloads from single machines to data warehouses or web services with many concurrent users.

Structured Query Language (SQL) is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.

A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary.

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements.

In computer programming, a directive or pragma is a language construct that specifies how a compiler should process its input. Depending on the programming language, directives may or may not be part of the grammar of the language and may vary from compiler to compiler. They can be processed by a preprocessor to specify compiler behavior, or function as a form of in-band parameterization.

<span class="mw-page-title-main">Boolean data type</span> Data having only values "true" or "false"

In computer science, the Boolean is a data type that has one of two possible values which is intended to represent the two truth values of logic and Boolean algebra. It is named after George Boole, who first defined an algebraic system of logic in the mid 19th century. The Boolean data type is primarily associated with conditional statements, which allow different actions by changing control flow depending on whether a programmer-specified Boolean condition evaluates to true or false. It is a special case of a more general logical data type—logic does not always need to be Boolean.

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

<span class="mw-page-title-main">Null (SQL)</span> Marker used in SQL databases to indicate a value does not exist

In SQL, null or NULL is a special marker used to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent null in database theory. In SQL, NULL is a reserved word used to identify this marker.

<span class="mw-page-title-main">LAMP (software bundle)</span> Acronym for a common web hosting solution

A LAMP is one of the most common software stacks for the web's most popular applications. Its generic software stack model has largely interchangeable components.

PL/Perl is a procedural language supported by the PostgreSQL RDBMS.

Embedded SQL is a method of combining the computing power of a programming language and the database manipulation capabilities of SQL. Embedded SQL statements are SQL statements written inline with the program source code, of the host language. The embedded SQL statements are parsed by an embedded SQL preprocessor and replaced by host-language calls to a code library. The output from the preprocessor is then compiled by the host compiler. This allows programmers to embed SQL statements in programs written in any number of languages such as C/C++, COBOL and Fortran. This differs from SQL-derived programming languages that don't go through discrete preprocessors, such as PL/SQL and T-SQL.

<span class="mw-page-title-main">Comment (computer programming)</span> Explanatory note in the source code of a computer program

In computer programming, a comment is a programmer-readable explanation or annotation in the source code of a computer program. They are added with the purpose of making the source code easier for humans to understand, and are generally ignored by compilers and interpreters. The syntax of comments in various programming languages varies considerably.

SQL/PSM 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, SQL/PSM was later incorporated into the multi-part SQL:1999 standard, and has been part 4 of that standard since then, most recently in SQL:2023. The SQL:1999 part 4 covered less than the original PSM-96 because the SQL statements for defining, managing, and invoking routines were actually incorporated into part 2 SQL/Foundation, leaving only the procedural language itself as SQL/PSM. The SQL/PSM facilities are still optional as far as the SQL standard is concerned; most of them are grouped in Features P001-P008.

The null coalescing operator is a binary operator that is part of the syntax for a basic conditional expression in several programming languages, such as : C# since version 2.0, Dart since version 1.12.0, PHP since version 7.0.0, Perl since version 5.10 as logical defined-or, PowerShell since 7.0.0, and Swift as nil-coalescing operator.

SQL:1999 was the fourth revision of the SQL database query language. It introduced many new features, many of which required clarifications in the subsequent SQL:2003. In the meanwhile SQL:1999 is deprecated.

PL/SQL is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database, TimesTen in-memory database, and IBM Db2. Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

<span class="mw-page-title-main">Snake case</span> Words joined with underscores

Snake case is the naming convention in which each space is replaced with an underscore (_) character, and words are written in lowercase. It is a commonly used naming convention in computing, for example for variable and subroutine names, and for filenames. One study has found that readers can recognize snake case values more quickly than camel case. However, "subjects were trained mainly in the underscore style", so the possibility of bias cannot be eliminated.

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. 1 2 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.


This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.