Functions and triggers
PL/pgSQL, volatility, BEFORE and AFTER, and when to use a constraint.
Some logic belongs in the database itself, where every application and report uses it the same way: a function computes a value, a trigger reacts to changes.
Functions
A function has parameters, a return type and a body. A body in SQL is a single query; a body in PL/pgSQL, PostgreSQL's procedural language, adds variables (DECLARE), conditions (IF ... ELSIF ... ELSE ... END IF), loops, and errors (RAISE EXCEPTION). The body is written between $$ marks, so it can contain quotes.
A function can also say how it behaves, which helps the planner: immutable (same inputs, same result, forever: ROUND), stable (the same within one query: CURRENT_DATE) or volatile, the default (may change every call: RANDOM()).
Triggers
A trigger runs a function automatically when rows of a table are inserted, updated or deleted.
| Choice | Options |
|---|---|
| When | BEFORE the change (to check or adjust the row) or AFTER it (to react: log, notify) |
| How often | FOR EACH ROW (once per changed row) or FOR EACH statement (once per statement) |
| Which change | INSERT, UPDATE, UPDATE OF column, DELETE |
In a row trigger, NEW is the row as it will be, and OLD the row as it was (in an update or delete). A BEFORE trigger returns NEW to let the row through, a changed NEW to adjust it, or raises an exception to refuse it.
Constraint or trigger?
Prefer a constraint whenever it can express the rule: it is simpler, faster and cannot be forgotten. Use a trigger when the rule needs another table (a claim's loss date inside its policy's period) or must record history (an audit trail of reserve changes).
SELECT trigger_name, event_manipulation, action_timing
FROM information_schema.triggers
WHERE event_object_table = 'claims';Check your understanding
Answer every question, then check.