Lesson 14 advanced

Views, Functions and Triggers

Package queries as views and materialized views, write SQL and PL/pgSQL functions with the right volatility, raise errors with clear messages, run batch work in procedures, keep an updated_at column current with a trigger, audit changes, push events to the application with LISTEN and NOTIFY, and know when logic belongs in the database.

Postgres can do more than store and return rows. It can name queries, run code close to the data and react to changes automatically. Used with care, these features remove whole classes of bugs from applications. This lesson adds a view, a few functions and two triggers to Orbit.

Views

A view is a saved query with a name. It stores no data; every time you select from it, Postgres runs the query underneath. Orbit’s API often needs notes with their author and tags, so give that query a name:

CREATE VIEW note_summaries AS
SELECT n.id, n.title, u.name AS author, n.pinned,
       coalesce(string_agg(t.name, ', ' ORDER BY t.name), '') AS tags
FROM notes n
JOIN users u ON u.id = n.user_id
LEFT JOIN note_tags nt ON nt.note_id = n.id
LEFT JOIN tags t ON t.id = nt.tag_id
GROUP BY n.id, u.name;
SELECT id, title, author, tags FROM note_summaries WHERE id <= 5 ORDER BY id;
 id |      title       |   author   |      tags
----+------------------+------------+-----------------
  1 | Shopping list    | Ana Silva  | personal
  2 | Deploy checklist | Ana Silva  | work
  3 | Book ideas       | Ben Carter | ideas, personal
  4 | Postgres tips    | Ben Carter | postgres, work
  5 | Meeting notes    | Ana Silva  | work
(5 rows)

The planner merges the view into your query, so the WHERE id <= 5 is applied before the joins, not after building the summary for 200,000 notes. Views are also a security tool: grant access to a view that exposes some columns, not to the table itself (lesson 15).

CREATE OR REPLACE VIEW can add columns at the end but not remove or rename them; drop and recreate the view for that.

Materialized views

A materialized view stores the result of its query, so reading it is instant, at the cost of being stale until you refresh it:

CREATE MATERIALIZED VIEW user_stats AS
SELECT u.id, u.name, count(n.id) AS notes
FROM users u
LEFT JOIN notes n ON n.user_id = u.id
GROUP BY u.id, u.name;

CREATE UNIQUE INDEX ON user_stats (id);

SELECT name, notes FROM user_stats ORDER BY id;
    name    | notes
------------+-------
 Ana Silva  | 66669
 Ben Carter | 66669
 Chen Wei   | 66667
(3 rows)

Those are the five notes from lesson 6 plus the 200,000 generated in lesson 11. Refresh it on a schedule, for example every few minutes from cron or pg_cron:

REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

CONCURRENTLY lets readers keep using the old contents during the refresh; it needs the unique index created above.

SQL functions

A function packages an expression or query for reuse. The simplest kind is written in SQL:

CREATE FUNCTION initials(full_name text) RETURNS text
LANGUAGE sql IMMUTABLE
RETURN upper(left(split_part(full_name, ' ', 1), 1) || left(split_part(full_name, ' ', 2), 1));

SELECT name, initials(name) FROM users ORDER BY id;
    name    | initials
------------+----------
 Ana Silva  | AS
 Ben Carter | BC
 Chen Wei   | CW
(3 rows)

The RETURN form (PostgreSQL 14 and later) is parsed when the function is created, so a typo in a column name fails immediately instead of at the first call. The older style puts the body in a string: AS $$ SELECT ... $$.

Volatility

Tell Postgres how a function behaves, because the planner relies on it:

VolatilityMeaningExamples
IMMUTABLESame inputs always give the same resultinitials, lower
STABLESame result within one statement; may read tablesFunctions that look up a row
VOLATILE (default)Can change at any time or has side effectsrandom(), functions that insert

Only IMMUTABLE functions can be used in index expressions and generated columns. Declaring a function immutable when it is not leads to wrong results, so be honest.

PL/pgSQL functions

For variables, conditions, loops and errors, use PL/pgSQL, Postgres’s procedural language. Pinning a note should only be possible for the note’s owner:

CREATE FUNCTION pin_note(p_note_id bigint, p_user_id bigint) RETURNS boolean
LANGUAGE plpgsql AS $$
DECLARE
  v_owner bigint;
BEGIN
  SELECT user_id INTO v_owner FROM notes WHERE id = p_note_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'note % does not exist', p_note_id USING ERRCODE = 'no_data_found';
  END IF;
  IF v_owner <> p_user_id THEN
    RAISE EXCEPTION 'note % does not belong to user %', p_note_id, p_user_id
      USING ERRCODE = 'insufficient_privilege';
  END IF;
  UPDATE notes SET pinned = true WHERE id = p_note_id;
  RETURN true;
END;
$$;
SELECT pin_note(3, 1);
ERROR:  note 3 does not belong to user 1

(A CONTEXT line follows, naming the function and line.) Ben, the owner, can pin it:

SELECT pin_note(3, 2);
 pin_note
----------
 t
(1 row)
  • Prefix parameters and variables (p_, v_) so they never clash with column names.
  • RAISE EXCEPTION aborts the statement and its transaction; USING ERRCODE gives applications a SQLSTATE to react to. RAISE NOTICE just prints a message.
  • BEGIN ... EXCEPTION WHEN unique_violation THEN ... END catches specific errors inside a function.

Procedures and DO blocks

A procedure is called with CALL and, unlike a function, may COMMIT in the middle. That suits batch jobs that work through a large table in chunks:

CREATE PROCEDURE purge_generated_notes(batch int DEFAULT 10000)
LANGUAGE plpgsql AS $$
BEGIN
  LOOP
    DELETE FROM notes
    WHERE id IN (SELECT id FROM notes WHERE title LIKE 'Generated note %' LIMIT batch);
    EXIT WHEN NOT FOUND;
    COMMIT;
  END LOOP;
END;
$$;

Do not call it yet: later lessons still use the generated notes. A DO block runs anonymous PL/pgSQL once, which is handy in migrations: DO $$ BEGIN ... END $$;.

Triggers

A trigger runs a function automatically when rows are inserted, updated or deleted. The classic example keeps an updated_at column current, whoever changes the row:

ALTER TABLE notes ADD COLUMN updated_at timestamptz;

CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER notes_set_updated_at
  BEFORE UPDATE ON notes
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();
UPDATE notes SET pinned = true WHERE id = 1 RETURNING updated_at IS NOT NULL AS stamped;
 stamped
---------
 t
(1 row)

UPDATE 1

In a BEFORE ... FOR EACH ROW trigger, NEW is the row about to be written and you may change it; returning NULL would skip the row. AFTER triggers see the final row and are used for side effects.

An audit trail

CREATE TABLE notes_audit (
  at      timestamptz NOT NULL DEFAULT now(),
  op      text NOT NULL,
  note_id bigint NOT NULL,
  old_row jsonb,
  new_row jsonb
);

CREATE FUNCTION audit_notes() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO notes_audit (op, note_id, old_row, new_row)
  VALUES (TG_OP,
          coalesce(NEW.id, OLD.id),
          CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) - 'search' END,
          CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) - 'search' END);
  RETURN NULL;
END;
$$;

CREATE TRIGGER notes_audit_trg
  AFTER INSERT OR UPDATE OR DELETE ON notes
  FOR EACH ROW EXECUTE FUNCTION audit_notes();

TG_OP says which operation fired the trigger. The - 'search' drops the bulky search vector from the stored copies.

Telling the application about changes

NOTIFY sends a message to every session that ran LISTEN on a channel, delivered when the transaction commits. A trigger can announce new notes so the app refreshes open screens:

CREATE FUNCTION notify_new_note() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  PERFORM pg_notify('new_note', json_build_object('id', NEW.id, 'user_id', NEW.user_id)::text);
  RETURN NULL;
END;
$$;

CREATE TRIGGER notes_notify
  AFTER INSERT ON notes
  FOR EACH ROW EXECUTE FUNCTION notify_new_note();

Try it in psql with LISTEN new_note; in one session and an insert in another.

When to put logic in the database

Good fitBetter in the application
Invariants: timestamps, audit trails, derived columnsBusiness workflows that change every sprint
Logic many services shareCalls to external APIs and email
Set-based work close to big dataCPU-heavy computation
Security checks that must not be bypassedAnything that needs extensive unit testing and debugging tools

Triggers are invisible from the application code, so document them, keep them short and avoid chains of triggers firing other triggers.

Quick reference

TaskSQL
Saved queryCREATE VIEW v AS SELECT ...;
Stored resultCREATE MATERIALIZED VIEW mv AS ...;
Refresh without blocking readsREFRESH MATERIALIZED VIEW CONCURRENTLY mv;
SQL functionCREATE FUNCTION f(x text) RETURNS text LANGUAGE sql IMMUTABLE RETURN ...;
PL/pgSQL functionLANGUAGE plpgsql AS $$ DECLARE ... BEGIN ... END $$;
Raise an errorRAISE EXCEPTION 'msg %', v USING ERRCODE = '...';
Batch with commitsCREATE PROCEDURE ...; CALL p();
Row triggerCREATE TRIGGER t BEFORE UPDATE ON tbl FOR EACH ROW EXECUTE FUNCTION f();
Show function source\sf name
Notify the apppg_notify('channel', payload) + LISTEN channel;

Exercise

A dashboard counts notes per user over millions of rows on every page view, and the numbers may be a few minutes old. What fits best?

  1. A materialized view refreshed CONCURRENTLY on a schedule
  2. A regular view
  3. A trigger that counts all notes on every insert
  4. A VOLATILE function called from the dashboard
Show answer

Answer: A. A materialized view stores the computed result, so reads are instant. CONCURRENTLY refreshes it without blocking readers. A regular view would recompute the count on every request.

Exercise

Which volatility is correct for a function that returns the current user’s plan by reading the users table?

  1. IMMUTABLE
  2. VOLATILE, because every function that reads tables must be volatile
  3. STABLE
  4. It does not matter
Show answer

Answer: C. STABLE functions may read the database and return the same result within a single statement. IMMUTABLE would be wrong, because the plan can change between statements.

Exercise

In a BEFORE UPDATE … FOR EACH ROW trigger, how do you change a value that will be stored?

  1. Run a separate UPDATE on the same table inside the trigger
  2. Assign to OLD.column
  3. Return NULL
  4. Assign to NEW.column and return NEW
Show answer

Answer: D. NEW is the row about to be written. Changing it and returning it modifies what is stored. Returning NULL skips the row, and a nested UPDATE would fire the trigger again.

Key takeaways

  • Views name queries and can limit what users see; materialized views store results and need refreshing.
  • Write simple functions in SQL with the RETURN form, and declare their volatility honestly.
  • PL/pgSQL adds variables, conditions, loops and clear errors with SQLSTATE codes.
  • Procedures can commit in the middle, which suits batch jobs.
  • Triggers enforce invariants such as updated_at and audit trails, and NOTIFY pushes events to listeners.

FAQ

Do views slow down queries?

No. A view is expanded into the query that uses it and optimised as a whole. Views that stack many other views can become hard to read and tune, though, so keep them shallow.

Can I update data through a view?

Simple views on one table, with no aggregates, are automatically updatable. For complex views, write an INSTEAD OF trigger that turns the change into updates of the base tables.

Which languages can I write functions in?

SQL and PL/pgSQL are always available. Extensions add others, such as PL/Python, PL/Perl and PL/v8 (JavaScript). Untrusted languages like plpython3u can access the server’s file system and need a superuser to create functions.

How do I debug a PL/pgSQL function?

Add RAISE NOTICE 'value: %', v; lines and watch the messages in psql. The plpgsql_check extension finds many errors before the function ever runs.

Why did my trigger fire twice?

Probably because both a BEFORE and an AFTER trigger exist, or a trigger updates its own table and fires itself again. List a table’s triggers with \d notes, and use pg_trigger_depth() to guard against recursion.

↑ ↓ navigate Enter open Esc close