PL/pgSQL & triggers

Procedural logic, and code that fires on data changes.

Procedures and triggers

For real logic — loops, conditionals, variables — use PL/pgSQL:

CREATE FUNCTION price_band(p numeric) RETURNS text AS $$
BEGIN
  IF p < 12 THEN RETURN 'budget';
  ELSIF p < 20 THEN RETURN 'standard';
  ELSE RETURN 'premium';
  END IF;
END;
$$ LANGUAGE plpgsql;

A trigger runs a function automatically when rows change — perfect for audit logs, derived values, or enforcing rules. A trigger function returns trigger and can read NEW/OLD:

CREATE TRIGGER book_added AFTER INSERT ON books
  FOR EACH ROW EXECUTE FUNCTION log_book();

Timing decides what the function can do. A BEFORE trigger can still edit NEW or abort the statement because nothing is written yet; an AFTER trigger cannot change the row but can rely on it existing — which is exactly what an audit log needs.

An IF/ELSIF chain is exclusive and ordered: the first true test wins, so the bands must be written cheapest-first. Written the other way round every book would come back premium.