PL/pgSQL

PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language) is the built-in procedural language of the PostgreSQL database. It extends ordinary SQL with the control structures of a general-purpose programming language — variables, assignments, loops, conditionals, and exception handling — so that complex logic can be packaged inside the database server as functions, stored procedures, and triggers. Where plain SQL describes what data to retrieve or change, PL/pgSQL lets developers describe how a sequence of operations should be carried out, all within a single, server-side, transaction-aware unit of code.

History and Origins

PL/pgSQL was designed by Jan Wieck and first appeared with PostgreSQL 6.4, released on October 30, 1998. At the time, PostgreSQL already supported user-defined functions in C and in plain SQL, but neither offered comfortable procedural control. PL/pgSQL filled that gap by providing a language that ran inside the server, understood PostgreSQL’s data types natively, and could be loaded into a database on demand.

From the outset the language was modeled closely on Oracle’s PL/SQL. This was a deliberate design choice: PL/SQL was already familiar to a large community of database developers, and aligning with it lowered the barrier to adopting PostgreSQL. PL/pgSQL is often described as resembling a subset of Oracle PL/SQL, and PostgreSQL’s own documentation includes guidance on porting code between the two. Both languages — together with the ISO SQL/PSM standard for persistent stored modules — trace their block-structured, strongly typed design lineage back to Ada.

Design Philosophy

PL/pgSQL is built around a few guiding ideas:

• Reduce round trips. By moving procedural logic into the server, a block of computation that would otherwise require many client-to-server SQL statements can execute as a single call, with intermediate results staying inside the database. This is especially valuable over high-latency connections.
• First-class access to SQL. SQL queries are not bolted on through a separate API; they are written directly in the language. Query results flow into variables and records, and values from variables substitute seamlessly into SQL statements.
• Block structure. Every PL/pgSQL function or procedure is organized into blocks of the form DECLARE ... BEGIN ... EXCEPTION ... END. Blocks can nest, each introducing its own scope of declared variables, and the optional EXCEPTION section gives structured error recovery.
• Type integration. Variables are declared with PostgreSQL’s own SQL data types. Anchored declarations such as %TYPE and %ROWTYPE let a variable automatically adopt the type of a table column or an entire row, so code stays correct when the schema changes.

Key Features

A typical PL/pgSQL routine combines declarations, ordinary SQL, and control flow:

1
2
3
4
5

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

The language’s notable capabilities include:

• Control structures — IF/THEN/ELSIF/ELSE, CASE, and several loop forms (LOOP, WHILE, the integer and query-based FOR), plus EXIT and CONTINUE.
• Records and cursors — RECORD and row-typed variables hold query results; explicit cursors and FOR ... IN SELECT loops iterate over result sets row by row.
• Exception handling — the EXCEPTION block traps named SQL error conditions (such as division_by_zero or unique_violation), enabling clean rollback-and-recover patterns within a function.
• Dynamic SQL — the EXECUTE statement builds and runs commands assembled at runtime, with USING parameters to guard against injection; the trade-off is that a fresh execution plan is generated each time.
• Set-returning functions — RETURN NEXT and RETURN QUERY let a function emit multiple rows that callers can query like a table.
• Trigger and procedure support — trigger functions receive special variables such as NEW, OLD, and TG_OP; since PostgreSQL 11, PL/pgSQL can also implement true stored procedures that control transactions with COMMIT and ROLLBACK.
• Function optimization hints — CREATE FUNCTION accepts volatility categories (VOLATILE, STABLE, IMMUTABLE) and a strictness setting that together help the planner cache and reorder calls.

PL/pgSQL functions can be declared trusted, meaning the server permits unprivileged users to write them because the language cannot, by itself, reach outside the database (unlike “untrusted” languages such as PL/PythonU).

Evolution

PL/pgSQL has matured steadily alongside PostgreSQL itself rather than through standalone releases:

• PostgreSQL 8.0 (2005) improved the language’s robustness, including its structured EXCEPTION handling.
• PostgreSQL 9.0 (2010) made PL/pgSQL installed by default in newly created databases, removing the long-standing need to run CREATE LANGUAGE plpgsql first and effectively making it the canonical way to write server-side logic.
• The 8.4 (2009) and 9.x releases refined function support — adding variadic arguments and default parameter values in 8.4, named-parameter call notation in 9.0, and richer set-returning support such as RETURNS TABLE.
• PostgreSQL 11 (2018) introduced SQL stored procedures (CREATE PROCEDURE / CALL) with in-procedure transaction control, a capability PL/pgSQL routines can use.

Because the language ships with the server, its “version” effectively tracks the PostgreSQL release. PostgreSQL 18 was released on September 25, 2025, and continues to bundle PL/pgSQL as the default procedural language.

Current Relevance

PL/pgSQL remains one of the most widely used procedural languages in the database world, simply because PostgreSQL is among the most popular relational databases in production today. Nearly every non-trivial PostgreSQL deployment that uses triggers, audit logging, complex validation, or batch data transformations relies on PL/pgSQL somewhere. Its presence by default means developers reach for it without additional setup, and PostgreSQL-derived platforms such as Greenplum carry the language forward into analytic and warehouse settings.

The language is also central to migration projects. Teams moving applications from Oracle to PostgreSQL frequently translate PL/SQL packages into PL/pgSQL, and the close family resemblance — shared block structure, similar cursor and exception models — makes that translation tractable, even where syntax differs (for example, Oracle’s IS becomes AS, and PostgreSQL requires an explicit LANGUAGE clause).

Why It Matters

PL/pgSQL demonstrates how an open-source project can absorb the best ideas of a commercial predecessor and make them freely available. By echoing Oracle PL/SQL, it gave PostgreSQL a credible answer to one of the database features enterprises most depended on, helping the project compete in serious production environments. More broadly, it embodies the enduring case for putting logic close to the data: when correctness, performance, and atomicity all hinge on the same transaction, a language that lives inside the database — and speaks SQL as a first-class citizen — remains a powerful tool more than a quarter-century after its debut.

Sources: PL/pgSQL — Wikipedia; PostgreSQL Documentation: PL/pgSQL; PostgreSQL Documentation: Porting from Oracle PL/SQL; PostgreSQL 9.0 Release Notes; PostgreSQL 18 Released!.