skip to content

Server-Side Code

PostgreSQL runs your logic in-process: PL/pgSQL bodies, functions and procedures, row and event triggers, and views. Interviewers probe what each one costs and whose rights it runs with.

part ofPostgreSQLread it →
on this pageshow

explore

questions

21

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, how do you build a BEFORE UPDATE row trigger that stamps an updated_at column, and what must its trigger function return?

level: juniorimportance: must knowfreq 68%

basics

~20 s

Write a function declared RETURNS trigger that sets NEW.updated_at and returns NEW, then attach it with CREATE TRIGGER ... BEFORE UPDATE ... FOR EACH ROW EXECUTE FUNCTION. Returning NULL instead silently skips that row's update.

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 PL/pgSQL, how does SELECT INTO behave with and without STRICT when zero or several rows match, and what does FOUND report?

level: middleimportance: must knowfreq 62%

basics

~10 s

Without STRICT, SELECT INTO takes the first row or sets NULLs and never errors; FOUND says whether a row came back. INTO STRICT requires exactly one row, raising NO_DATA_FOUND (P0002) or TOO_MANY_ROWS (P0003) otherwise.

open as a page

In PostgreSQL, what does REFRESH MATERIALIZED VIEW CONCURRENTLY require, and what does it cost compared with a plain refresh of the same view?

level: middleimportance: must knowfreq 55%

basics

~20 s

PostgreSQL's REFRESH MATERIALIZED VIEW CONCURRENTLY needs a populated view with a unique index on plain columns and no WHERE clause. It keeps readers unblocked by diffing new rows against old, at the cost of more work and dead tuples.

open as a page

In PL/pgSQL, how do you build a dynamic statement whose table name and filter value come from parameters without opening an SQL injection hole?

level: seniorimportance: must knowfreq 58%

basics

~20 s

Use EXECUTE with format(): put table or column names in with %I, pass every data value as a $n placeholder bound by USING, and never concatenate raw input or use %s. EXECUTE plans each time and does not set FOUND.

open as a page

In PostgreSQL's PL/pgSQL, how is a block structured, and what do %TYPE and %ROWTYPE give your variable declarations?

level: juniorimportance: should knowfreq 52%

basics

~20 s

A PL/pgSQL block is an optional label and DECLARE section, then BEGIN statements, an optional EXCEPTION section and END; BEGIN/END only group code. %TYPE copies a column's or variable's type, %ROWTYPE a table's whole row type.

open as a page

In PostgreSQL, what happens to a view defined as SELECT * FROM a table when a column of that table is later added, retyped or dropped?

level: juniorimportance: should knowfreq 38%

basics

~20 s

PostgreSQL expands * at creation and stores the parsed query as the view's _RETURN rule, so a new table column stays invisible until the view is replaced, and retyping or dropping a column it uses fails.

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

In PL/pgSQL, when an EXCEPTION clause catches an error, what happens to data changes and local variables, and where do RAISE and SQLSTATE fit?

level: middleimportance: should knowfreq 48%

basics

~20 s

When an EXCEPTION handler catches an error, PostgreSQL rolls back the block's database changes but keeps local variables and earlier statements. RAISE EXCEPTION throws an error, P0001 by default or a chosen SQLSTATE that handlers match.

open as a page

In PostgreSQL, when one PL/pgSQL trigger function serves INSERT, UPDATE and DELETE, how do TG_OP, NEW and OLD behave and what should it return?

level: middleimportance: should knowfreq 47%

basics

~20 s

TG_OP holds 'INSERT', 'UPDATE', 'DELETE' or 'TRUNCATE'. In row triggers NEW exists for INSERT and UPDATE, OLD for UPDATE and DELETE; both are null at statement level. A BEFORE row function returns NEW, or OLD for DELETE.

open as a page

In PostgreSQL, what makes a view automatically updatable, and what stops an UPDATE through a region-filtered view from moving a row out of it?

level: middleimportance: should knowfreq 45%

basics

~20 s

A PostgreSQL view is auto-updatable when it reads one table or updatable view without WITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET, set operations, aggregates, window or set-returning functions. WITH CHECK OPTION rejects rows the view could not see.

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 events can an event trigger fire on, how does it differ from a table trigger, and how do you recover when a login event trigger blocks every connection?

level: seniorimportance: should knowfreq 24%

basics

~20 s

Event triggers are per-database, superuser-created hooks on login, ddl_command_start, ddl_command_end, sql_drop and table_rewrite, not on table rows. If a login trigger fails, a superuser connects with event_triggers off, or uses single-user mode, and fixes it.

open as a page

In PostgreSQL, how do you maintain a per-warehouse stock summary with a FOR EACH STATEMENT trigger using REFERENCING NEW TABLE, and what restrictions apply?

level: seniorimportance: should knowfreq 34%

basics

~20 s

Declare AFTER INSERT ON stock_movement REFERENCING NEW TABLE AS new_moves FOR EACH STATEMENT and aggregate new_moves into the summary with one upsert. Transition tables need an AFTER trigger on a plain table, exactly one event and no column list.

open as a page

A nightly job refreshes the PostgreSQL materialized view behind a sales leaderboard; which role should run it, and which failure modes must the job handle?

level: seniorimportance: should knowfreq 30%

basics

~20 s

Since PostgreSQL 17, grant the job role MAINTAIN on that one materialized view instead of ownership; the query still runs as the owner with search_path pinned. The job must handle lock waits, overlapping runs, timeouts and dependent views.

open as a page

In PostgreSQL, whose privileges and row-level security policies apply when a support agent queries a view, and what changes when the view sets security_invoker?

level: seniorimportance: should knowfreq 40%

basics

~20 s

By default a PostgreSQL view checks base-table privileges and applies row-level security as the view's owner. With security_invoker = true it uses the querying user's privileges and policies instead; how functions in the view run is unaffected.

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

In PostgreSQL, when does PL/pgSQL parse and prepare the SQL inside a function, and what production surprises does that statement caching cause?

level: seniorimportance: nice to knowfreq 30%

basics

~20 s

PL/pgSQL compiles a function body on its first call in each session and prepares each SQL statement the first time it executes, then reuses it. So errors in unexecuted branches surface late, names cannot be variables, and literals like 'now' freeze.

open as a page

In PostgreSQL, what does CREATE CONSTRAINT TRIGGER with DEFERRABLE INITIALLY DEFERRED change about an AFTER trigger, and which role runs it in PostgreSQL 18?

level: seniorimportance: nice to knowfreq 16%

basics

~20 s

A constraint trigger is an AFTER ROW trigger whose firing can be deferred to commit and controlled by SET CONSTRAINTS. Since PostgreSQL 18 every AFTER trigger runs as the role active when its event was queued, unless the function is SECURITY DEFINER.

open as a page