skip to content

Functions & Procedures

PostgreSQL functions carry labels the planner trusts: volatility, STRICT, PARALLEL SAFE and COST; procedures can COMMIT mid-body. Interviewers probe mislabelled volatility and SECURITY DEFINER holes.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

In PostgreSQL, what can a procedure invoked with CALL do that a function invoked from a SELECT cannot, and how does each return results?

level: juniorimportance: must knowfreq 66%

answer

  1. two kinds of routine in pg_proc
  2. CALL versus SELECT
  3. transaction control inside the body
  4. OUT parameters come back as one row

basics

~20 s

A PostgreSQL procedure, run only with CALL, can COMMIT or ROLLBACK inside its body and returns just its OUT and INOUT parameters as one row; a function runs inside the caller's statement and transaction and returns a value or a set.

solid answer

~40 s

`CREATE PROCEDURE` makes a routine you can only run with `CALL`; it cannot appear in a `SELECT`, a `WHERE` clause or an index. Its distinctive power is transaction control: a PL/pgSQL procedure may `COMMIT` or `ROLLBACK` mid-body, and a new transaction starts automatically, which suits a batch that posts monthly interest to accounts in chunks. A procedure has no return value; its `OUT` and `INOUT` parameters come back from `CALL` as a single row, and you pass `NULL` for each `OUT` argument. A function (`CREATE FUNCTION`) is called inside an expression, always runs within the caller's transaction, and returns a scalar, a composite, or rows via `RETURNS SETOF` or `RETURNS TABLE`. The in-body commit only works when `CALL` runs in its own transaction, not inside an explicit `BEGIN` block.

code

sql · 26 lines
sql
CREATE PROCEDURE post_monthly_interest(
    IN p_batch int,
    OUT accounts_posted bigint)
LANGUAGE plpgsql
AS $$
DECLARE
    n bigint;
BEGIN
    accounts_posted := 0;
    LOOP
        UPDATE account
           SET balance = balance + round(balance * annual_rate / 12, 2),
               interest_posted_on = current_date
         WHERE id IN (SELECT id FROM account
                       WHERE interest_posted_on < date_trunc('month', current_date)::date
                       LIMIT p_batch);
        GET DIAGNOSTICS n = ROW_COUNT;
        EXIT WHEN n = 0;
        accounts_posted := accounts_posted + n;
        COMMIT;
    END LOOP;
END;
$$;

-- Run in its own transaction (autocommit), never inside BEGIN ... COMMIT
CALL post_monthly_interest(5000, NULL);

go deeper

for a junior

Recall the two commands, CREATE PROCEDURE run with CALL and CREATE FUNCTION used inside a query, and that only a procedure can commit partway through.

for a middle

Explain the non-atomic context rule: CALL in its own transaction, no SECURITY DEFINER or SET clause, no SELECT in the call stack, and how OUT parameters come back as one row.

for a senior

Show you have run batch procedures in production: chunked commits that release locks and keep transactions short, and diagnosing 2D000 from a driver with autocommit off.

for a principal

Weigh where batch control belongs, a committing procedure inside the database or an external scheduler driving chunked transactions, considering observability, retries and who owns failure recovery.

## Two kinds of routine PostgreSQL stores functions and procedures in the same catalog, `pg_proc`; the `prokind` column tells them apart (`f` for a normal function, `p` for a procedure, plus `a` and `w` for aggregate and window functions). They share most of the creation syntax, including the language, the parameter list, `SECURITY DEFINER` and `SET` clauses, but they are invoked differently and live under different transaction rules. | | Function | Procedure | |---|---|---| | Created with | `CREATE FUNCTION` | `CREATE PROCEDURE` | | Invoked with | any expression: `SELECT` list, `WHERE`, `DEFAULT`, index expression, trigger | `CALL` only | | Result | a return value: scalar, composite, `SETOF`, `TABLE` | no return value; `OUT`/`INOUT` parameters as one row | | Transaction control | never; it runs inside the caller's statement | `COMMIT` and `ROLLBACK` in a non-atomic context | | Planner labels | volatility, `STRICT`, `PARALLEL`, `COST`, `ROWS` | none; a procedure is not part of a planned query | Calling a procedure with `SELECT` fails with `... is a procedure` and the hint `To call a procedure, use CALL.`; calling a function with `CALL` fails with `... is not a procedure`. ## Transaction control inside a procedure The reason procedures exist is that a function can never end the transaction it runs in. A PL/pgSQL procedure can write `COMMIT` or `ROLLBACK`, and after each one a new transaction starts automatically; there is no `START TRANSACTION` inside the body. `COMMIT AND CHAIN` starts the next transaction with the same characteristics, such as the isolation level, instead of the defaults. That makes a procedure the natural home for a batch that must not run as one giant transaction. Posting monthly interest to two million accounts in chunks of 5,000, committing each chunk, releases row locks as it goes, and a failure loses only the chunk in flight. (Why a long transaction also hurts cleanup is a vacuum topic.) The server allows this only in a **non-atomic context**. `COMMIT` raises SQLSTATE `2D000`, `invalid transaction termination`, when: 1. `CALL` was issued inside an explicit transaction block (`BEGIN; CALL ...;`), which includes a client driver running with autocommit off. 2. The procedure is declared `SECURITY DEFINER`. 3. The procedure has a `SET` clause attached, for example `SET search_path = ...`. 4. A command other than `CALL` or `DO` sits in the call stack: in `CALL p1()` then `SELECT f()` then `CALL p3()`, the procedure `p3` cannot commit. A fifth case has its own message: `COMMIT` inside a PL/pgSQL block that has an `EXCEPTION` clause fails with `cannot commit while a subtransaction is active`, because such a block runs as a subtransaction. How exception blocks are written belongs to the PL/pgSQL topic. ## How results come back A function declares what it returns: `RETURNS numeric`, `RETURNS ledger_entry` for one composite row, `RETURNS SETOF ledger_entry` or `RETURNS TABLE (...)` for many rows, or `OUT` parameters, which turn the result into a record. Because a function is an expression, its result can be filtered, joined and aggregated like any other value. A procedure has no return value at all. Inside a PL/pgSQL procedure, `RETURN` may appear only without an expression, to leave early. Results travel through output parameters: - From plain SQL, `CALL` returns one row holding the final values of the `OUT` and `INOUT` parameters. Every parameter without a default needs an argument, including the `OUT` ones, and the convention is to write `NULL` there. - From PL/pgSQL or a `DO` block, each `OUT` or `INOUT` argument must be a variable, and the final value is assigned back to it. - A procedure cannot stream rows the way `RETURNS SETOF` does; to hand back many rows it writes them to a table or passes a cursor name back through an `INOUT refcursor` parameter. ## Choosing between them - Use a **function** for anything a query consumes: a computed value, a lookup, a row set to join, a trigger body, an expression in a constraint or a policy. - Use a **procedure** when the routine is a unit of work run on its own and benefits from committing as it goes: batch posting, archiving in chunks, a maintenance loop. - If the routine needs `SECURITY DEFINER` or a `SET` clause *and* must commit, those requirements collide. Split it: a plain procedure owns the loop and the commits, and calls a privileged function for each chunk; the function has returned before each `COMMIT` runs. ## Common confusions - A function cannot commit even once at the end; it never controls the transaction. - `CALL` does not open a transaction of its own when one is already open; it joins it, and that is exactly what disables the in-body commit. - A procedure is not a function returning `void`: it is invoked differently, carries no planner labels, and only it may end transactions.

  • An application calls this procedure through a client driver with autocommit turned off and gets SQLSTATE 2D000. What happened, and what are the fixes?
    With autocommit off the driver has already opened a transaction block, so `CALL` runs in an atomic context and the procedure's `COMMIT` raises `invalid transaction termination`. Either issue the `CALL` on a connection in autocommit mode, or remove the in-body `COMMIT` and let the caller own the transaction. The procedure can only end transactions that its own top-level `CALL` started.
  • Why might a batching procedure use COMMIT AND CHAIN instead of a plain COMMIT?
    The transaction that starts automatically after `COMMIT` gets default characteristics. If the batch ran at `REPEATABLE READ` or with another non-default transaction setting, the next chunk would silently revert to the defaults. `COMMIT AND CHAIN` starts the next transaction with the same characteristics as the one just committed.

  • A PL/pgSQL function can COMMIT at the end of its body.
  • CALL always starts its own transaction, even inside BEGIN ... COMMIT.
  • Procedures hand back a value with RETURN expression, just like functions.
  • A procedure can be called from a SELECT list like any function.
  • Declaring a procedure SECURITY DEFINER has no effect on whether it can COMMIT.
open as a page

In PostgreSQL, what do the IMMUTABLE, STABLE and VOLATILE labels on CREATE FUNCTION let the planner do, and which is the default?

level: middleimportance: must knowfreq 58%

basics

~20 s

VOLATILE, the default, allows no optimisation and re-evaluates per row; STABLE lets PostgreSQL evaluate once per statement, so it can drive an index scan; IMMUTABLE allows plan-time folding and is required in index expressions. PostgreSQL trusts the label without verifying it.

open as a page

In PostgreSQL, how do you choose between LANGUAGE sql, LANGUAGE plpgsql and an untrusted language such as plpython3u for a new server-side function?

level: middleimportance: should knowfreq 36%

basics

~20 s

Use LANGUAGE sql when the logic is a query or expression, since it can be inlined and a RETURN or BEGIN ATOMIC body tracks dependencies; plpgsql for variables, loops and error handling; an untrusted language only for work SQL cannot do.

open as a page

A PostgreSQL banking schema indexes tax_rate(region_code), a function declared IMMUTABLE that reads a tax_rates table; what goes wrong when a rate changes, and how do you fix it?

level: seniorimportance: should knowfreq 40%

basics

~20 s

PostgreSQL trusts the IMMUTABLE label, so index entries keep the old rate after tax_rates changes and results differ between index and sequential scans. Store the applied rate in a column, or label the function honestly STABLE and stop indexing it.

open as a page

In PostgreSQL, why must a SECURITY DEFINER function that lets tellers write to a protected audit table also SET search_path, and what else must its owner lock down?

level: seniorimportance: should knowfreq 46%

basics

~20 s

A SECURITY DEFINER function runs with its owner's privileges but resolves unqualified names through the caller's search_path, so a caller's temporary table can hijack it. Pin SET search_path = audit, pg_temp, revoke EXECUTE from PUBLIC, and use a minimally privileged owner.

open as a page

In PostgreSQL, which CREATE FUNCTION attributes stop a LANGUAGE sql set-returning function from being inlined, and what do ROWS, COST and PARALLEL change when it is not?

level: seniorimportance: nice to knowfreq 24%

basics

~20 s

A LANGUAGE sql set-returning function is inlined only if its body is one SELECT and it is not STRICT, VOLATILE, SECURITY DEFINER or given a SET clause; otherwise it is an opaque Function Scan costed from ROWS (default 1000) and COST.

open as a page