beginnerintermediateadvanced

PostgreSQL · Functions and Procedures

PostgreSQL Functions and Procedures

SQL functions vs PL/pgSQL, parameters/control flow/exceptions, procedures' unique power to COMMIT/ROLLBACK, and where database-side logic stops paying for itself

SQL fn = one statementPL/pgSQL fn = a full procedural block
EXCEPTION WHEN conditioncatch specific errors, not OTHERS
Only procedures COMMITfunctions run inside the caller's transaction
A targeted toolnot a substitute application runtime

1.SQL Functions and PL/pgSQL Functions

A SQL function's body is a single SQL statement (or a short sequence of them) — no branching, no loops, no local variables. A PL/pgSQL function's body is a full procedural block with IF/CASE, loops, variables and exception handling, written in PostgreSQL's own procedural language.

Key features

A SQL function's body is plain SQL statement(s) — no variables, no control flow
A PL/pgSQL function's body is a procedural block: DECLARE/BEGIN/END, with IF, LOOP, variables and exceptions
A simple SQL function can be inlined by the planner into the calling query, which a PL/pgSQL function generally cannot
Both are created with CREATE FUNCTION; the LANGUAGE clause (sql vs plpgsql) selects which one you get
PostgreSQL also ships PL/Tcl, PL/Perl and PL/Python as optional procedural languages, and supports custom ones

SQL function vs PL/pgSQL function

SQL function vs PL/pgSQL function
Property	SQL function	PL/pgSQL function
Body	one or more plain SQL statements	a procedural block (DECLARE/BEGIN/END)
Control flow	none	IF, CASE, LOOP, WHILE, FOR
Local variables	none	yes, via DECLARE
Planner visibility	can be inlined into the caller	opaque function call
Good fit	a single parameterized query	multi-step logic, branching, error handling

A named statement vs a small program

SQL function

Body is one or more plain SQL statements
No variables, no control flow
Can be inlined by the planner into the caller

PL/pgSQL function

Body is a procedural block (DECLARE/BEGIN/END)
IF, CASE, LOOP, variables, exceptions
Always an opaque call to the planner

Command

sql

-- SQL function: body is one statement
CREATE FUNCTION full_name(first text, last text)
RETURNS text
LANGUAGE sql
AS $$
    SELECT first || ' ' || last;
$$;

-- PL/pgSQL function: body is a procedural block
CREATE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
    IF b = 0 THEN
        RETURN NULL;
    END IF;
    RETURN a / b;
END;
$$;

SQL function = a named SQL statement, inlineable by the planner. PL/pgSQL function = a small program with variables, control flow and exceptions, called as an opaque unit. Pick based on whether the logic needs a decision or a loop, not by default.

2.Parameters, Return Types, Variables, Control Flow and Exceptions

A PL/pgSQL function declares typed IN/OUT/INOUT parameters, a return type (a scalar, a row type, or a set via RETURNS TABLE/SETOF), local variables via DECLARE, control flow via IF/CASE/LOOP/WHILE/FOR, and errors are caught with a BEGIN ... EXCEPTION WHEN condition THEN ... END block.

Key features

Parameters can be IN (default), OUT (returned automatically) or INOUT; OUT parameters let a function return multiple named values without a custom type
RETURNS can be a scalar type, a table row type, TABLE(...) for a typed set, or SETOF for returning multiple rows of an existing type
DECLARE introduces local variables with types, optionally defaulted or typed %TYPE off an existing column
Control flow: IF/ELSIF/ELSE, CASE, LOOP/EXIT, WHILE, FOR (both integer ranges and query result sets)
EXCEPTION WHEN condition THEN ... catches errors raised inside its BEGIN block; each such block is its own subtransaction

Parameter modes

Parameter modes
Mode	Meaning
IN (default)	value passed into the function, read-only inside it
OUT	value the function sets and returns; omit an explicit RETURNS clause when every return value is OUT
INOUT	combines IN and OUT — passed in, and the function may change it before returning

The shape of a PL/pgSQL function body

CREATE FUNCTION safe_divide(a numeric, b numeric) RETURNS numeric LANGUAGE plpgsql AS $$ DECLARE result numeric; BEGIN result := a / b; RETURN result; EXCEPTION WHEN division_by_zero THEN RETURN NULL; END; $$;

a numeric, b numeric

IN parameters — typed, read-only inside the function

DECLARE result numeric;

Local variable — scoped to this function body

result := a / b; RETURN result;

Control flow — the ordinary body — can branch or loop

EXCEPTION WHEN division_by_zero THEN RETURN NULL;

Exception block — catches one specific condition — its own subtransaction

Command

sql

CREATE FUNCTION order_summary(order_id int, OUT total numeric, OUT item_count int)
LANGUAGE plpgsql AS $$
DECLARE
    v_status text;
BEGIN
    SELECT status INTO v_status FROM orders WHERE id = order_id;

    SELECT sum(price * qty), count(*)
      INTO total, item_count
      FROM order_items
     WHERE order_items.order_id = order_summary.order_id;
EXCEPTION
    WHEN no_data_found THEN
        total := 0;
        item_count := 0;
END;
$$;

IN/OUT/INOUT parameters, DECLARE for locals, IF/CASE/LOOP/WHILE/FOR for control flow, and BEGIN ... EXCEPTION WHEN condition THEN ... END scoped to its own block (and its own subtransaction) for error handling — catch specific conditions, not OTHERS, unless you genuinely mean every error.

3.Procedures and Transaction-Related Differences

A PROCEDURE is invoked with CALL rather than SELECT, does not return a value the way a function does (though it can have OUT parameters), and — unlike a function — its body may run COMMIT and ROLLBACK, starting a new transaction each time, as long as nothing outside a chain of nested CALL/DO invocations intervenes.

Key features

A procedure is invoked with CALL; a function is invoked with SELECT (or as part of an expression)
A procedure does not have a RETURNS clause for a value the way a function does, though OUT parameters still work and are returned by CALL
A procedure's body can execute COMMIT and ROLLBACK; a function's body cannot
Transaction control inside a procedure only works from a top-level CALL, or nested CALL/DO invocations with no other command in between
After COMMIT or ROLLBACK inside a procedure, a new transaction starts automatically with default characteristics (use COMMIT AND CHAIN / ROLLBACK AND CHAIN to preserve settings)

PROCEDURE vs FUNCTION

PROCEDURE vs FUNCTION
Property	PROCEDURE	FUNCTION
Created with	CREATE PROCEDURE	CREATE FUNCTION
Invoked with	CALL proc(...)	SELECT func(...)
Returns a value	no RETURNS clause (OUT params still work)	yes, via RETURNS
COMMIT/ROLLBACK in body	allowed, under the CALL-chain rule	never allowed
Usable in a SELECT list / WHERE clause	no	yes

Only one of these can manage its own transaction boundary

FUNCTION — SELECT func(...)

Always runs inside the caller's existing transaction
COMMIT/ROLLBACK inside the body: never allowed
Returns a value, usable in a SELECT list

PROCEDURE — CALL proc(...)

Can commit and start a fresh transaction mid-body
Only from a top-level CALL, or nested CALL/DO with nothing between
No return value the way a function has (OUT params still work)

Command

sql

CREATE PROCEDURE archive_old_orders()
LANGUAGE plpgsql AS $$
DECLARE
    r record;
BEGIN
    FOR r IN SELECT id FROM orders WHERE created_at < now() - interval '1 year' LOOP
        INSERT INTO orders_archive SELECT * FROM orders WHERE id = r.id;
        DELETE FROM orders WHERE id = r.id;
        COMMIT;  -- allowed here: CALL at the top level, nothing intervening
    END LOOP;
END;
$$;

CALL archive_old_orders();

CALL invokes a procedure, SELECT invokes a function. Only a procedure's body can run COMMIT/ROLLBACK — and only when reached via a top-level CALL or a chain of nested CALL/DO with nothing else in between — because a function always executes inside the caller's existing transaction.

4.When Database-Side Logic Is Appropriate

Database-side logic (a function or procedure) is a good fit when the operation is set-based, must be atomic with the data it touches, or needs to run close to the data for performance — enforcing an invariant, a bulk computation, a trigger-driven side effect. Application-layer logic is usually clearer for business rules that change often, involve external systems, or benefit from the application's own testing, version control and code review workflow.

Key features

Favors the database: invariants every writer must respect regardless of which application wrote the row, set-based bulk operations, logic that must be atomic with the write itself
Favors the application: business rules that change frequently, logic involving external systems (payment gateways, emails), anything that benefits from the application's tests/code review/version control
Database-side logic is invisible to application-level tooling — an IDE cannot "find usages" of a trigger the way it can a Python function
Moving logic into the database can reduce round trips for genuinely set-based work, which is a real, measurable win for bulk operations
The decision is per-operation, not all-or-nothing — a healthy system usually has some invariants in the database and most business logic in the application

Leaning database-side vs application-side

Leaning database-side vs application-side
Signal	Lean database-side	Lean application-side
Must hold regardless of caller	yes — a constraint or trigger	no — a UI-specific rule
Changes frequently	no	yes
Touches external systems	no	yes — email, payment gateway, third-party API
Shape of the work	set-based, bulk	per-request business process
Needs strong testability/versioning in app tooling	less critical	critical

Command

sql

-- good fit: a data invariant, enforced regardless of which application writes
ALTER TABLE accounts ADD CONSTRAINT balance_non_negative CHECK (balance >= 0);

-- poor fit: business logic that changes often and calls an external system
-- (send a welcome email on signup) -- belongs in the application layer, not a trigger

Reach for database-side logic for invariants and set-based bulk operations that must be atomic with the data; reach for the application layer for business rules that change often or touch external systems — most healthy systems use both, deliberately, not one exclusively.

5.Avoiding an Unnecessarily Complex Database Runtime

It is possible to build entire application workflows out of PL/pgSQL functions, triggers calling other triggers, and PL/Python or PL/Perl for arbitrary logic — but doing so trades away the application layer's tooling (tests, version control diffing, code review, observability, horizontal scaling) for a language and environment that has none of that, purely because the code happens to run close to the data.

Key features

Deep trigger chains (a trigger that fires another trigger that fires another) are notoriously hard to trace and debug
PL/Python, PL/Perl and PL/Tcl can call out to the network or filesystem, which couples a database process to external systems it should generally not depend on
Database functions do not scale horizontally the way stateless application servers do — heavy computation there competes directly with the database's core job
Business logic hidden in the database is invisible to the application's own code review, IDE tooling and typical CI test suites
The roadmap's own framing — functions/procedures should stay a targeted tool, not become the primary place application logic lives

Command

sql

-- a small, well-scoped use: enforcing a derived value stays correct
CREATE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;
CREATE TRIGGER touch_updated_at BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

A database function or trigger is a targeted tool for invariants and set-based operations, not a substitute application runtime — long trigger chains, PL/Python doing network I/O, and business logic invisible to code review are signs the database is doing the application's job with worse tooling for it.

Spotted a mistake on this page? Tell us.

Read Sheet