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:
| Volatility | Meaning | Examples |
|---|---|---|
IMMUTABLE | Same inputs always give the same result | initials, lower |
STABLE | Same result within one statement; may read tables | Functions that look up a row |
VOLATILE (default) | Can change at any time or has side effects | random(), 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 EXCEPTIONaborts the statement and its transaction;USING ERRCODEgives applications a SQLSTATE to react to.RAISE NOTICEjust prints a message.BEGIN ... EXCEPTION WHEN unique_violation THEN ... ENDcatches 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 fit | Better in the application |
|---|---|
| Invariants: timestamps, audit trails, derived columns | Business workflows that change every sprint |
| Logic many services share | Calls to external APIs and email |
| Set-based work close to big data | CPU-heavy computation |
| Security checks that must not be bypassed | Anything 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
| Task | SQL |
|---|---|
| Saved query | CREATE VIEW v AS SELECT ...; |
| Stored result | CREATE MATERIALIZED VIEW mv AS ...; |
| Refresh without blocking reads | REFRESH MATERIALIZED VIEW CONCURRENTLY mv; |
| SQL function | CREATE FUNCTION f(x text) RETURNS text LANGUAGE sql IMMUTABLE RETURN ...; |
| PL/pgSQL function | LANGUAGE plpgsql AS $$ DECLARE ... BEGIN ... END $$; |
| Raise an error | RAISE EXCEPTION 'msg %', v USING ERRCODE = '...'; |
| Batch with commits | CREATE PROCEDURE ...; CALL p(); |
| Row trigger | CREATE TRIGGER t BEFORE UPDATE ON tbl FOR EACH ROW EXECUTE FUNCTION f(); |
| Show function source | \sf name |
| Notify the app | pg_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?
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?
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?
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
RETURNform, 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_atand audit trails, andNOTIFYpushes 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.