PL/pgSQL Explained

PL/pgSQL is PostgreSQL's built-in procedural language, and its entire value proposition is that it runs inside the same process as the query executor instead of shipping rows back and forth to a client.

That proximity is also its limit: PL/pgSQL is not a general-purpose programming language, it is glue code for expressing control flow that plain SQL cannot, and reaching for it too early trades away the planner's ability to optimize your logic as a single query.

This page builds the mental model behind blocks, volatility, STRICT, and the function-versus-procedure split so that Functions Basics, PL/pgSQL Essentials, Volatility & STRICT, and Stored Procedures and CALL read as one coherent system.

Summary

• PL/pgSQL wraps a block of procedural logic (DECLARE/BEGIN/EXCEPTION/END) around SQL statements, running inside the database process rather than round-tripping to a client.
• Insight: Keeping control flow next to the data avoids network round trips, and the right volatility and STRICT markers let the planner cache, skip, or parallelize calls it would otherwise have to run blindly.
• Key Concepts: block structure, SPI (Server Programming Interface), volatility, STRICT, SECURITY DEFINER.
• When to Use: Multi-step logic that genuinely needs branching or loops close to the data, trigger callbacks, or batch procedures that need to manage their own transactions.
• Limitations/Trade-offs: PL/pgSQL trades SQL's set-based clarity for row-by-row procedural control, and that control is easy to overuse for logic a plain query would express just as well.
• Related Topics: trigger functions, query planning and caching, transaction control, privilege boundaries.

Foundations

A PL/pgSQL function body is one block: an optional DECLARE section for local variables, a BEGIN/END section holding statements, and an optional EXCEPTION section for error handling.

CREATE OR REPLACE FUNCTION app.bump_version(p_id bigint)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  UPDATE app.accounts SET name = name || ' v2' WHERE id = p_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'missing account %', p_id;
  END IF;
END;
$$;

Every SQL statement inside that block runs through PostgreSQL's Server Programming Interface (SPI), the same internal API that lets PL/pgSQL hand a query string to the ordinary parser, planner, and executor and get rows back.

The special variable FOUND is set after every SPI-executed statement to whether it affected or returned at least one row, which is exactly what the example above uses to detect a missing account.

Control flow reads like any procedural language: IF/ELSIF/ELSE, LOOP/WHILE/FOR, and an implicit cursor opens automatically whenever a FOR loop iterates over a query's results.

The simplest mental model is that PL/pgSQL is a thin scripting layer wrapped around ordinary SQL statements, not a replacement for SQL's own set-based logic.

Mechanics & Interactions

A function (CREATE FUNCTION) always runs inside the transaction that called it and returns a value with RETURN, while a procedure (CREATE PROCEDURE, invoked with CALL) can issue its own COMMIT or ROLLBACK and returns nothing unless it uses OUT parameters.

That transaction-control split exists because a function can be called from inside a larger SQL expression, such as a SELECT list or a WHERE clause, where starting or ending a transaction mid-statement would be meaningless.

Volatility tells the planner what it's allowed to assume about repeated calls: IMMUTABLE promises the same input always produces the same output with no side effects, STABLE promises the same result within a single statement's snapshot, and VOLATILE (the default) promises nothing and forces a fresh call every time.

IMMUTABLE  ->  can be constant-folded, indexed in an expression index
STABLE     ->  safe to call once per statement, reused across rows
VOLATILE   ->  re-evaluated for every row, blocks many optimizations

Marking a function STRICT tells PostgreSQL that if any argument is NULL, the function should return NULL immediately without ever executing the body, which both saves work and removes an entire class of NULL-handling bugs from the function itself.

Trigger functions are a special case that returns the pseudo-type trigger instead of a normal SQL type, and PostgreSQL calls them automatically with implicit NEW/OLD row variables rather than explicit arguments, which is the shared machinery behind The Trigger Model.

Exception handling works through the EXCEPTION WHEN clause, and because PostgreSQL implements it with a savepoint under the hood, catching an error inside a large loop has a real per-iteration cost that a well-designed batch job should account for.

Advanced Considerations & Applications

SECURITY DEFINER changes whose privileges a function runs with: by default a function runs as SECURITY INVOKER (the calling user's privileges), while SECURITY DEFINER makes it run as the function's owner, which is how you let a low-privilege user perform a narrowly scoped privileged action without granting them broad table access.

That power comes with a well-known trap: a SECURITY DEFINER function that doesn't pin its own search_path can be tricked into resolving an unqualified object name to a malicious object the caller controls, which is why every SECURITY DEFINER function should set search_path explicitly in its definition.

Volatility also affects parallel query eligibility, since a VOLATILE function disables parallelism for the query calling it unless it is additionally marked PARALLEL SAFE, so a function that looks harmless can silently force a serial plan on an otherwise parallelizable query.

Testing database functions deserves the same discipline as testing application code, and because PL/pgSQL functions run inside a transaction by default, wrapping a test in a transaction that always rolls back is a cheap way to exercise a function's real side effects without leaving test data behind.

Common Misconceptions

• "PL/pgSQL is slow because it's interpreted." The interpreter overhead is real but usually tiny compared to the SQL statements it dispatches through SPI, which run through the same planner and executor as any other query.
• "Functions and procedures are interchangeable." Only a procedure invoked with CALL can issue its own COMMIT or ROLLBACK, a function always runs inside the caller's existing transaction.
• "STRICT means the argument is required." STRICT means that if any argument is NULL the function returns NULL immediately without running its body, it says nothing about whether the caller is allowed to omit the argument.
• "SECURITY DEFINER always runs as superuser." It runs with the privileges of whoever owns the function, which is only as dangerous as that owner's actual grants, though an unpinned search_path can still let a caller escalate what the function does.
• "VOLATILE is just the safe default with no real cost." Leaving a function VOLATILE when it's actually deterministic disables constant folding, expression indexing, and parallel query eligibility that IMMUTABLE or STABLE would have unlocked.

FAQs

Related

• Functions Basics - SQL functions versus PL/pgSQL, hands-on
• PL/pgSQL Essentials - variables, control flow, and FOUND in practice
• Volatility & STRICT - IMMUTABLE/STABLE/VOLATILE and planner effects
• SECURITY DEFINER Risks - privilege boundaries and search_path traps
• Stored Procedures and CALL - transaction control with CALL
• Triggers Basics - trigger functions as a specialized PL/pgSQL case

Stack versions: This page was written for PostgreSQL 18.4 (stable line 18, maintenance line 17), where the block structure, volatility, STRICT, and SECURITY DEFINER behavior described here have been stable across recent major releases.