In PostgreSQL, what can a procedure invoked with CALL do that a function invoked from a SELECT cannot, and how does each return results?
answer
- two kinds of routine in pg_proc
- CALL versus SELECT
- transaction control inside the body
- OUT parameters come back as one row
basics
~20 sA 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 linesCREATE 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
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.
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.
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.
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.