BrevFeed
Sign in

38. Procedures and triggers

📖 Reading · 5 min

Functions vs. procedures: the transaction-control distinction

You've been writing functions (CREATE FUNCTION) throughout the previous lesson. Postgres also has procedures (CREATE PROCEDURE, added in Postgres 11), and the difference is specific and important, not stylistic:

  • A function always returns a value and cannot control transactions — no COMMIT/ROLLBACK inside a function body. It's called as part of a larger SQL statement (SELECT my_function(...)), which is itself inside whatever transaction is already running.
  • A procedure does not have to return a value, is called with CALL, not as part of a SELECT, and can issue its own COMMIT/ROLLBACK internally — genuinely useful for a long-running batch job that needs to commit incremental progress rather than holding one enormous transaction open the whole time.
CREATE PROCEDURE bulk_update_rental_rates(p_increase numeric)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE film SET rental_rate = rental_rate + p_increase;
    COMMIT;   -- legal here; would be a syntax error inside a FUNCTION
END;
$$;

CALL bulk_update_rental_rates(0.10);

Rule of thumb: default to a function. Reach for a procedure specifically when you need internal transaction control (typically batch/maintenance work) — the overwhelming majority of reusable logic (computing a value, encapsulating a query) is a function.

Triggers: functions that run automatically on data changes

A trigger attaches a function to a table, causing it to run automatically when a specified event happens — INSERT, UPDATE, or DELETE. You've actually already seen triggers in action without naming them: pagila's own last_updated triggers (visible in every \d output this course has run) automatically stamp last_update on every row change.

CREATE FUNCTION log_rental_rate_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.rental_rate <> OLD.rental_rate THEN
        INSERT INTO rate_change_log (film_id, old_rate, new_rate, changed_at)
        VALUES (OLD.film_id, OLD.rental_rate, NEW.rental_rate, now());
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_log_rate_change
    BEFORE UPDATE ON film
    FOR EACH ROW
    EXECUTE FUNCTION log_rental_rate_change();

A trigger function is a special kind of PL/pgSQL function: it returns trigger (not an ordinary type), and inside it has access to special variables NEW (the row after the change — available for INSERT/UPDATE) and OLD (the row before the change — available for UPDATE/DELETE). A CREATE TRIGGER statement then attaches that function to a specific table and event.

BEFORE vs. AFTER

  • BEFORE — runs before the actual write happens. Can modify NEW to change what actually gets written (e.g. last_updated triggers set NEW.last_update := now() before the row is stored), or raise an exception to reject the write entirely — this is a procedural alternative to a CHECK constraint (Module 2) for validation logic too complex for a simple boolean expression.
  • AFTER — runs after the write has already happened. Cannot modify what was written (too late), but is the right choice for side effects that should only happen once the change is confirmed real — logging, cascading updates to other tables, sending a notification.

ROW-level vs. STATEMENT-level

  • FOR EACH ROW — the trigger function runs once per affected row. This is what you want when the logic depends on that specific row's OLD/NEW values (the rate-change logger above).
  • FOR EACH STATEMENT — the trigger function runs once per statement, regardless of how many rows it affected — no OLD/NEW available (there could be many rows, or none). Appropriate for logic that cares "did this kind of statement happen at all," not "what changed in each row" — e.g. logging "a bulk update ran" once, not once per row it touched.

A word of caution

Triggers are powerful and also a common source of surprising, hidden behavior — a plain UPDATE film SET rental_rate = ... silently triggers logging, validation, cascading changes, none of which are visible at the call site. Use triggers for logic that genuinely must be enforced regardless of which application or query performs the write (exactly the argument for constraints from Module 2, extended to logic too complex for a constraint) — not as a general-purpose place to hide business logic that would be clearer as explicit application code or an explicit function call.

Check yourself

  1. What can a procedure do that a function cannot?
  2. What's the practical difference between a BEFORE trigger and an AFTER trigger, in terms of what each can and cannot do?
  3. When would you choose FOR EACH STATEMENT over FOR EACH ROW?
← Previous Next →