Functions, procedures and triggers

PostgreSQL runs server-side code as functions (called inside SQL, return a value or a set of rows) and, since version 11, procedures (run with CALL, return nothing or INOUT parameters, and may control transactions). Both can be written in plain SQL or in PL/pgSQL, the procedural language with variables, IF, loops and exception blocks.

Functions

A LANGUAGE sql function is a query with parameters. Since PostgreSQL 14 a single-expression one can use the standard RETURN expr body instead of a quoted string. A plpgsql function has DECLARE / BEGIN ... END and uses SELECT ... INTO to put a query result into a variable.

1CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, amount numeric(10,2) NOT NULL);2INSERT INTO orders VALUES (1, 7, 120.00), (2, 7, 30.00), (3, 8, 600.00);

1CREATE FUNCTION discount_pct(amount numeric) RETURNS numeric2  LANGUAGE sql IMMUTABLE3  RETURN CASE WHEN amount >= 500 THEN 104              WHEN amount >= 100 THEN 55              ELSE 0 END;6 7CREATE FUNCTION customer_total(cust int) RETURNS numeric8  LANGUAGE plpgsql STABLE AS $$9DECLARE10  total numeric;11BEGIN12  SELECT coalesce(sum(amount), 0) INTO total13  FROM orders WHERE customer_id = cust;14  RETURN total;15END $$;16 17SELECT id, amount,18       discount_pct(amount) AS pct,19       customer_total(customer_id) AS customer_total20FROM orders21ORDER BY id;

Every function declares a volatility, which is a promise to the planner:

• IMMUTABLE — same arguments, same result, forever; reads no tables. Required for a function used in an index expression. lower() is one.
• STABLE — same result within one statement; may read tables. now() is one: it returns the transaction's start time throughout.
• VOLATILE (the default) — may change every call or have side effects: random(), clock_timestamp(), anything that writes.

Marking a function more stable than it is does not raise an error; it produces wrong results when the planner caches or pre-evaluates the call. Marking a stable function VOLATILE only costs speed.

Procedures and transactions

A procedure runs in the caller's transaction, so a failure inside it rolls back everything it did. A procedure may also COMMIT or ROLLBACK itself, which is the point of having them (for example, a batch job that commits every 10,000 rows), but only when it was CALLed outside an explicit transaction block. The examples here always run inside one (the sandbox wraps each run in a transaction), so you can see the error that produces.

1CREATE FUNCTION attempt(stmt text) RETURNS text LANGUAGE plpgsql AS $$2BEGIN3  EXECUTE stmt;4  RETURN 'ok';5EXCEPTION WHEN OTHERS THEN6  RETURN SQLSTATE || ': ' || SQLERRM;7END $$;8CREATE TABLE accounts (id int PRIMARY KEY, balance numeric NOT NULL CHECK (balance >= 0));9INSERT INTO accounts VALUES (1, 100), (2, 50);10CREATE PROCEDURE transfer(from_id int, to_id int, amt numeric) LANGUAGE plpgsql AS $$11BEGIN12  UPDATE accounts SET balance = balance - amt WHERE id = from_id;13  UPDATE accounts SET balance = balance + amt WHERE id = to_id;14END $$;15CREATE PROCEDURE transfer_and_commit(from_id int, to_id int, amt numeric) LANGUAGE plpgsql AS $$16BEGIN17  UPDATE accounts SET balance = balance - amt WHERE id = from_id;18  UPDATE accounts SET balance = balance + amt WHERE id = to_id;19  COMMIT;20END $$;

1CALL transfer(1, 2, 30);2 3CREATE TEMP TABLE outcome AS4SELECT attempt('CALL transfer(1, 2, 500)')          AS overdraw,5       attempt('CALL transfer_and_commit(1, 2, 10)') AS commit_inside,6       attempt('SELECT transfer(1, 2, 1)')          AS select_a_procedure;7 8SELECT o.*,9       (SELECT string_agg(id || '=' || balance, ', ' ORDER BY id) FROM accounts) AS balances10FROM outcome o;

Triggers

A trigger runs a function automatically on INSERT, UPDATE, DELETE or TRUNCATE. Row-level triggers see OLD and NEW; a BEFORE trigger can change NEW or cancel the row by returning NULL, an AFTER trigger sees the final row. UPDATE OF col limits the trigger to statements that set that column, and WHEN (...) to rows where a condition holds. Use IS DISTINCT FROM there: OLD.price <> NEW.price is NULL, not true, when either side is NULL.

1CREATE TABLE products (id int PRIMARY KEY, name text NOT NULL, price numeric(10,2));2CREATE TABLE price_history (3  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,4  product_id int NOT NULL, old_price numeric(10,2), new_price numeric(10,2)5);6INSERT INTO products VALUES (1, 'tea', 4.00), (2, 'cup', 9.00), (3, 'jar', NULL);

1CREATE FUNCTION log_price_change() RETURNS trigger2  LANGUAGE plpgsql AS $$3BEGIN4  INSERT INTO price_history (product_id, old_price, new_price)5  VALUES (OLD.id, OLD.price, NEW.price);6  RETURN NEW;7END $$;8 9CREATE TRIGGER products_price_audit10  AFTER UPDATE OF price ON products11  FOR EACH ROW12  WHEN (OLD.price IS DISTINCT FROM NEW.price)13  EXECUTE FUNCTION log_price_change();14 15UPDATE products SET price = price WHERE id = 1;    -- no real change16UPDATE products SET price = 10.00 WHERE id IN (2, 3);17 18SELECT product_id, old_price, new_price19FROM price_history20ORDER BY id;

Triggers are invisible to whoever reads the calling code, so keep them for rules that must hold for every writer: audit trails, derived columns, cross-table invariants.

Common mistakes

• Declaring a function IMMUTABLE although it reads a table, then getting stale results from an index or a cached plan.
• Putting COMMIT in a procedure and calling it from inside a transaction (or from a function): error 2D000.
• Writing WHEN (OLD.price <> NEW.price) and missing every change from or to NULL.
• Calling a procedure with SELECT: error 42809, it must be CALLed.

What interviewers ask

• Function versus procedure in PostgreSQL? Functions return values and are used in queries, without transaction control; procedures are CALLed and may COMMIT when not inside an outer transaction.
• What do IMMUTABLE, STABLE and VOLATILE mean? What may change between calls, and therefore what the planner may cache or index.
• BEFORE or AFTER trigger? BEFORE to modify or reject the row being written, AFTER to react to the final row (audit, cascades).