Functions, procedures and triggers

conceptThe concept

A SQL function is a parameterised query; simple ones are inlined into the calling query. Since 14, BEGIN ATOMIC ... END bodies are parsed at creation time and track dependencies. PL/pgSQL adds variables, IF, loops, exceptions and dynamic SQL with EXECUTE format(...).

Volatility tells the planner what it may assume: IMMUTABLE (same inputs, same output forever; usable in indexes), STABLE (same within a statement, such as lookups), VOLATILE (default; may change anything, evaluated every row). A wrong label gives wrong results or slow plans.

Triggers call a function BEFORE or AFTER INSERT, UPDATE or DELETE, FOR EACH ROW or FOR EACH STATEMENT. BEFORE row triggers can modify NEW; AFTER triggers see the final row and suit auditing. Statement triggers can use transition tables (REFERENCING NEW TABLE AS ...) to process all changed rows at once. Procedures (CALL) can COMMIT inside their body, which suits batch jobs.

whyWhy it matters

Rules enforced in the database apply to every client. Audit trails written by triggers cannot be bypassed by a forgotten code path. The flip side: hidden logic is hard to debug, and row-by-row triggers can make bulk loads slow.

Use caseReal-world scenario

Every change to product prices must be recorded with the old and new value and who made it, regardless of which service or script changed it. An AFTER UPDATE trigger writes the audit row.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE FUNCTION order_count(cust int) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
  SELECT count(*) FROM orders WHERE customer_id = cust;
END;
SELECT id, name, order_count(id) FROM customers WHERE id <= 3 ORDER BY id;
ROLLBACK;

BEGIN;
CREATE FUNCTION loyalty_tier(cust int) RETURNS text
LANGUAGE plpgsql STABLE AS $$
DECLARE
  spent numeric;
BEGIN
  SELECT coalesce(sum(total), 0) INTO spent
  FROM orders WHERE customer_id = cust AND status <> 'cancelled';
  IF spent >= 20000 THEN RETURN 'platinum';
  ELSIF spent >= 5000 THEN RETURN 'gold';
  ELSE RETURN 'standard';
  END IF;
END $$;
SELECT loyalty_tier(id) AS computed, count(*) FROM customers GROUP BY 1 ORDER BY 2 DESC;
ROLLBACK;

BEGIN;
CREATE TABLE price_audit (
  product_id int, old_price numeric, new_price numeric,
  changed_by text DEFAULT current_user, changed_at timestamptz DEFAULT now()
);
CREATE FUNCTION audit_price() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO price_audit (product_id, old_price, new_price) VALUES (OLD.id, OLD.price, NEW.price);
  RETURN NULL;
END $$;
CREATE TRIGGER products_price_audit
  AFTER UPDATE OF price ON products
  FOR EACH ROW WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION audit_price();

UPDATE products SET price = price * 1.10 WHERE category = 'garden';
UPDATE products SET stock = stock + 1 WHERE category = 'garden';
SELECT count(*) AS audited, round(avg(new_price / old_price), 3) AS avg_ratio FROM price_audit;
ROLLBACK;

BEGIN;
CREATE FUNCTION slow_now() RETURNS timestamptz LANGUAGE sql VOLATILE AS 'SELECT clock_timestamp()';
CREATE FUNCTION stable_now() RETURNS timestamptz LANGUAGE sql STABLE AS 'SELECT now()';
EXPLAIN SELECT count(*) FROM orders WHERE created_at > slow_now() - interval '30 days';
EXPLAIN SELECT count(*) FROM orders WHERE created_at > stable_now() - interval '30 days';
ROLLBACK;

internalsUnder the hood

SECURITY DEFINER functions run with the owner's privileges, like setuid programs. Always pin SET search_path = pg_catalog, pg_temp (and schema-qualify names) on them, or a caller can create a same-named function or table earlier in the search path and hijack them.

Avoid / preferDo this, not that

CREATE TRIGGER notify AFTER INSERT ON orders FOR EACH ROW
EXECUTE FUNCTION call_payment_api();  -- network call inside the transaction

-- insert an outbox row in the trigger; a worker calls the API

CREATE FUNCTION price_with_tax(p numeric) RETURNS numeric
LANGUAGE sql IMMUTABLE AS $$ SELECT p * (SELECT rate FROM tax_rates WHERE current) $$;

CREATE FUNCTION price_with_tax(p numeric) RETURNS numeric
LANGUAGE sql STABLE AS $$ SELECT p * (SELECT rate FROM tax_rates WHERE current) $$;

PitfallsCommon mistakes

• Marking a function IMMUTABLE when it reads tables or depends on settings such as TimeZone. Indexes built on it can silently become wrong.
• Writing SECURITY DEFINER functions without a fixed search_path.
• Using row-level triggers on tables that receive bulk loads, then wondering why COPY is slow. Consider statement-level triggers with transition tables.

LimitsWhen not to use it

Avoid putting core business workflows in triggers that developers do not expect: hidden side effects make bugs hard to trace. Use them for auditing, derived data and invariants.

SecuritySecurity notes

A SECURITY DEFINER function runs with its owner's privileges. Always add SET search_path = pg_catalog, pg_temp (and schema-qualify names), and REVOKE EXECUTE ... FROM PUBLIC before granting it to the roles that need it.

ChallengeTry it yourself

BEGIN;
CREATE FUNCTION guard_price() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.price < OLD.price * 0.5 THEN
    RAISE EXCEPTION 'price cut too large for %: % to %', OLD.sku, OLD.price, NEW.price;
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER products_price_guard BEFORE UPDATE OF price ON products
  FOR EACH ROW EXECUTE FUNCTION guard_price();
UPDATE products SET price = round(price * 0.9, 2) WHERE id = 1 RETURNING sku, price;
UPDATE products SET price = round(price * 0.1, 2) WHERE id = 1;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• PL/pgSQL
• Function volatility
• Triggers