Stats

78 Contributors: 5 Friday, May 19, 2017
Licensed under: CC-BY-SA
Not affiliated with Stack Overflow
Rip Tutorial: riptutorial@gmail.com
Roadmap: roadmap

Programming with PL/pgSQL

Remarks

PL/pgSQL is PostgreSQL's built-in programming language for writing functions which run within the database itself, known as stored procedures in other databases. It extends SQL with loops, conditionals, and return types. Though its syntax may be strange to many developers it is much faster than anything running on the application server because the overhead of connecting to the database is eliminated, which is particularly useful when you would otherwise need to execute a query, wait for the result, and submit another query.

Though many other procedural languages exist for PostgreSQL, such as PL/Python, PL/Perl, and PLV8, PL/pgSQL is a common starting point for developers who want to write their first PostgreSQL function because its syntax builds on SQL. It is also similar to PL/SQL, Oracle's native procedural language, so any developer familiar with PL/SQL will find the language familiar, and any developer who intends to develop Oracle applications in the future but wants to start with a free database can transition from PL/pgSQL to PL/SQL with relative ease.

It should be emphasized that other procedural languages exist and PL/pgSQL is not necessarily superior to them in any way, including speed, but examples in PL/pgSQL can serve as a common reference point for other languages used for writing PostgreSQL functions. PL/pgSQL has the most tutorials and books of all the PLs and can be a springboard to learning the languages with less documentation.

Here are links to some free guides and books on PL/pgSQL:

Related Examples