Functions, Triggers, and PL/pgSQL

Postgres will run your code inside the database, in a choice of languages, inside the transaction that called it. For an invariant that has to hold no matter which client is writing, that is the correct place for it and nowhere else will do. It is also how a team ends up with a quarter of its business logic somewhere with no version control, no test harness and no stack trace.

Cartwheel needs three things from this topic, and they sit in different places on that line. The status history in delivery_events has to record every status change including the ones made by hand, which the application cannot promise. The dashboard's materialized view has to be refreshable by a role that does not own it. And the API wants a helper that turns a customer-facing public_id into an internal orders.id without a round trip. Two of the three belong in the database; the third only if it is labelled correctly.

Volatility Is a Planner Contract

Every function carries a volatility label, and the label is a promise the planner acts on. IMMUTABLE means the function always returns the same result for the same arguments and never consults the database. STABLE means the result is fixed for the duration of one statement but may differ between statements, which is what any function that reads a table must be. VOLATILE means anything at all, and it is what you get by saying nothing.

-- reads a table, so STABLE is the strongest label it may carry
CREATE FUNCTION order_id_for(p_public_id uuid) RETURNS bigint
  LANGUAGE sql STABLE AS
  $$ SELECT id FROM orders WHERE public_id = p_public_id $$;

-- pure computation over its argument: usable in an index expression
CREATE FUNCTION sku_prefix(sku text) RETURNS text
  LANGUAGE sql IMMUTABLE AS
  $$ SELECT upper(left(sku, 3)) $$;

The label controls what the planner may do with a call. An IMMUTABLE call with constant arguments can be evaluated once at plan time and replaced by its value. Only an immutable function may appear in an index expression, because the index stores the answers and nothing recomputes them afterwards. And a STABLE function may be used in an index scan's comparison value, where a VOLATILE one forces re-evaluation per row. Getting it wrong in the safe direction costs performance. Getting it wrong in the other — labelling order_id_for as IMMUTABLE because it "basically" returns the same thing, then indexing on it — produces an index full of answers computed against data that has since changed, and the planner has no reason to doubt it.

SQL Bodies and PL/pgSQL Bodies

A function written in plain SQL whose body is a single query can be inlined into the calling statement: the planner replaces the call with the query, and then optimizes the whole thing as one, with real statistics on both sides of the seam. That is the reason to prefer SQL for anything that is really just an expression.

A PL/pgSQL function is a black box by comparison. It has variables, loops, exception blocks and the ability to do several things in order, and the planner cannot see through any of it. The costs are concrete rather than philosophical: a procedural-language function is assumed to cost 100 units where a built-in costs 1, and a set-returning function with no ROWS estimate is planned as though it returns 1,000 rows — a number that is wrong by three orders of magnitude in either direction often enough to flip a join.

Standard Bodies and Dependency Tracking

The classic function body is a string literal. Postgres stores the text and parses it when the function runs, which means the body can name objects that do not exist yet, and it keeps working right up to the moment the table it reads is dropped. Since 14, a SQL function can be written in the standard BEGIN ATOMIC … END form instead, which is parsed at definition time.

-- string body: parsed when it runs; orders can be dropped underneath it
CREATE FUNCTION order_id_for(p_public_id uuid) RETURNS bigint
  LANGUAGE sql STABLE AS
  $$ SELECT id FROM orders WHERE public_id = p_public_id $$;

-- standard body: parsed and dependency-tracked at creation time
CREATE OR REPLACE FUNCTION order_id_for(p_public_id uuid) RETURNS bigint
  LANGUAGE sql STABLE
  BEGIN ATOMIC
    SELECT id FROM orders WHERE public_id = p_public_id;
  END;

With the second form Postgres records that the function depends on orders and on the column it reads, so a DROP TABLE is refused rather than accepted and discovered later by a failing query. The trade is that a body parsed at creation time cannot resolve things that are only knowable at call time, which rules out polymorphic argument types and a few related constructs. It is also LANGUAGE sql only: a PL/pgSQL body is still a string, and a DROP TABLE underneath one still succeeds.

Triggers: What Fires, and How Often

A BEFORE row trigger runs before the row is written and can do two things nothing else can: modify the row on its way in, or return NULL and cancel the operation for that row. An AFTER row trigger sees the final state, including the effects of other triggers, and cannot change it — which is exactly what an audit trail wants. The other axis is how often the function runs. FOR EACH ROW fires once per affected row, so a delete of 10 rows calls it 10 times. FOR EACH STATEMENT fires once per statement, even when the statement modified nothing at all, and an AFTER statement trigger can see the whole change set through transition tables.

CREATE FUNCTION orders_log_status_change() RETURNS trigger
  LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO delivery_events (order_id, event_type, occurred_at, payload)
  SELECT n.id, 'status_changed', now(),
         jsonb_build_object('from', o.status, 'to', n.status,
                            'by', current_user)
    FROM new_rows n JOIN old_rows o ON o.id = n.id
   WHERE n.status IS DISTINCT FROM o.status;
  RETURN NULL;
END $$;

CREATE TRIGGER orders_status_audit
  AFTER UPDATE ON orders
  REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION orders_log_status_change();

The REFERENCING clause hands the function two relations holding the before and after images of every row the statement touched, so a bulk update of 50,000 orders produces one function call and one set-based insert instead of 50,000 calls and 50,000 inserts. IS DISTINCT FROM does the null-safe comparison, so a row whose status was null before and is pending now is recorded rather than skipped. One call per statement is what makes an audit trigger affordable. Attach the same logic FOR EACH ROW and a COPY of 5 million rows into delivery_events runs the function 5 million times, turning a 30-second load into an hour.

Where Triggers Earn Their Place

A trigger earns its place in three jobs. An audit trail that must capture every change, including the ones that never went through the API — Cartwheel's negative stock arrived from a correction script, and an application-side audit log would have recorded nothing at all about the writes that mattered most. A denormalized counter that has to move in the same transaction as the thing it counts. And bookkeeping columns such as updated_at, where the alternative is trusting every writer to remember.

The disqualifiers are equally clear. A trigger that calls an external service holds its transaction's locks for the entire round trip, runs again on every retry, and cannot be undone when the transaction rolls back — the write can be taken back, the email cannot. A multi-step workflow in PL/pgSQL is code the test harness cannot reach, because the harness knows how to call the API and not how to call a trigger. And a trigger that writes a table which has triggers of its own builds a cascade that is invisible from the UPDATE that started it, with a depth usually discovered in production.

SECURITY DEFINER Done Safely

A SECURITY DEFINER function executes with the privileges of the role that owns it rather than the role that called it. That is the clean way to hand a restricted role exactly one privileged operation: cartwheel_analytics can be allowed to rebuild the dashboard's materialized view without being given any rights over the view or the tables underneath it.

CREATE FUNCTION analytics.refresh_daily_orders() RETURNS void
  LANGUAGE plpgsql
  SECURITY DEFINER
  SET search_path = pg_catalog, analytics, pg_temp
  AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.daily_orders;
END $$;

REVOKE EXECUTE ON FUNCTION analytics.refresh_daily_orders() FROM PUBLIC;
GRANT  EXECUTE ON FUNCTION analytics.refresh_daily_orders()
    TO cartwheel_analytics;

The SET search_path clause pins name resolution for the duration of the call, with pg_temp written last, and it is here for the reason Chapter 1 gave when it took name resolution apart: an unpinned definer function lets its caller decide which code runs with the owner's rights. The REVOKE is the half that gets left out. EXECUTE on a newly created function is granted to PUBLIC automatically, so between the CREATE and that line every role in the cluster can call it, and a definer function nobody revoked is a privilege handed to everyone rather than to cartwheel_analytics.