Views, Functions & Triggers

The queries from Window Functions are useful exactly once, typed out in full, every time. Views name a query so you can reuse it like a table. Functions and procedures go further, wrapping logic — not just a single SELECT — behind a callable name. Triggers run that logic automatically, in response to writes, without the application ever calling anything explicitly.

Views: a saved query, not saved data

CREATE VIEW active_high_spenders AS
SELECT user_id, total_spend
FROM user_totals
WHERE total_spend > 1000
AND status = 'active';

-- used exactly like a table
SELECT * FROM active_high_spenders ORDER BY total_spend DESC;

A plain view stores no data of its own — every query against it re-runs the underlying SELECT against current data, live. It's purely a naming and permissions convenience: you can GRANT SELECT on the view without granting access to the underlying tables, and hide a complex join behind a simple name.

Materialized views: a saved result

CREATE MATERIALIZED VIEW user_totals_snapshot AS
SELECT user_id, sum(amount) AS total_spend
FROM payments
GROUP BY user_id;

-- data is frozen at creation time until refreshed
REFRESH MATERIALIZED VIEW user_totals_snapshot;

-- refresh without blocking concurrent reads (requires a unique index)
CREATE UNIQUE INDEX ON user_totals_snapshot (user_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY user_totals_snapshot;

A materialized view runs the query once and stores the result like a real table, on disk. Reads against it are fast and don't touch the underlying tables at all — but the data goes stale the moment anything underneath changes, until the next REFRESH. Plain REFRESH MATERIALIZED VIEW takes an ACCESS EXCLUSIVE lock and blocks reads against it for the duration; CONCURRENTLY avoids that by building the new result alongside the old one and swapping, at the cost of requiring a unique index on the view and roughly doubling the disk space used during the refresh.

Functions: SQL and PL/pgSQL

The simplest function is a named, parameterized SQL query:

CREATE FUNCTION total_spend_for(p_user_id int)
RETURNS numeric AS $$
  SELECT coalesce(sum(amount), 0)
  FROM payments
  WHERE user_id = p_user_id;
$$ LANGUAGE sql;

SELECT total_spend_for(42);

For anything with branching, loops, or multiple statements, PL/pgSQL (Postgres's procedural extension of SQL) is the usual choice:

CREATE FUNCTION spend_tier(p_user_id int)
RETURNS text AS $$
DECLARE
  v_total numeric;
BEGIN
  SELECT coalesce(sum(amount), 0) INTO v_total
  FROM payments
  WHERE user_id = p_user_id;

  IF v_total > 1000 THEN
      RETURN 'gold';
  ELSIF v_total > 100 THEN
      RETURN 'silver';
  ELSE
      RETURN 'bronze';
  END IF;
END;
$$ LANGUAGE plpgsql;

Procedures: functions that can control transactions

CREATE PROCEDURE archive_old_payments(p_before date)
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO payments_archive
  SELECT * FROM payments WHERE payment_date < p_before;

  DELETE FROM payments WHERE payment_date < p_before;

  COMMIT;  -- allowed here; not allowed inside a function
END;
$$;

CALL archive_old_payments('2025-01-01');

Triggers: functions that fire automatically

A trigger function looks like a normal PL/pgSQL function but returns trigger and reads the special NEW/OLD row variables that hold the row being inserted/updated/deleted:

CREATE TABLE payments_audit (
  id           serial PRIMARY KEY,
  payment_id   int,
  changed_at   timestamptz DEFAULT now(),
  old_amount   numeric,
  new_amount   numeric
);

CREATE FUNCTION log_payment_change() RETURNS trigger AS $$
BEGIN
  INSERT INTO payments_audit (payment_id, old_amount, new_amount)
  VALUES (NEW.id, OLD.amount, NEW.amount);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER payments_audit_trigger
AFTER UPDATE ON payments
FOR EACH ROW
WHEN (OLD.amount IS DISTINCT FROM NEW.amount)
EXECUTE FUNCTION log_payment_change();

OLD holds the row's values before the change, NEW holds them after — OLD is NULL for an INSERT trigger, NEW is NULL for a DELETE trigger. BEFORE triggers can inspect and modify NEW before it's written (returning NULL from a BEFORE trigger cancels the operation entirely); AFTER triggers, like this one, run once the change has already happened and are the natural fit for logging, since they can't affect whether the write succeeds.

Next: what happens when a single table like payments grows too large to manage as one physical object, in Partitioning.