PostgreSQL Functions & Triggers - PostgreSQL Tutorial

Functions

SQL

CREATE OR REPLACE FUNCTION price_with_vat(price NUMERIC)
RETURNS NUMERIC AS $func$
BEGIN
    RETURN ROUND(price * 1.13, 2);
END;
$func$ LANGUAGE plpgsql IMMUTABLE;

SELECT title, price_with_vat(price) FROM books;

The $func$ ... $func$ is dollar quoting — it lets the body contain single quotes without escaping. Any tag works: $$, $body$ , etc.

Volatility markers

Tell the planner how the function behaves so it can optimise:

Marker	Meaning
`IMMUTABLE`	same input always gives same output
`STABLE`	consistent within one statement
`VOLATILE`	can change anytime (default)

Only IMMUTABLE functions can be used in expression indexes.

Returning a set

SQL

CREATE OR REPLACE FUNCTION books_by_author(author_name TEXT)
RETURNS TABLE (id BIGINT, title TEXT, price NUMERIC) AS $func$
BEGIN
    RETURN QUERY
    SELECT b.id, b.title, b.price
    FROM books b
    JOIN authors a ON a.id = b.author_id
    WHERE a.name = author_name;
END;
$func$ LANGUAGE plpgsql STABLE;

SELECT * FROM books_by_author('Buddhisagar');

Simple SQL functions

For a one-liner, plain SQL is lighter than plpgsql:

SQL

CREATE FUNCTION active_book_count() RETURNS BIGINT AS $$
    SELECT COUNT(*) FROM books WHERE is_active;
$$ LANGUAGE sql STABLE;

Triggers

A trigger runs a function automatically on insert, update, or delete. The classic use is maintaining updated_at:

SQL

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $func$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$func$ LANGUAGE plpgsql;

CREATE TRIGGER books_set_updated_at
BEFORE UPDATE ON books
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Inside a trigger function:

NEW — the incoming row (INSERT/UPDATE)
OLD — the previous row (UPDATE/DELETE)
TG_OP — 'INSERT', 'UPDATE', or 'DELETE'

A BEFORE trigger can modify NEW or return NULL to cancel the operation. An AFTER trigger cannot change the row but is right for audit logging.

Audit logging example

SQL

CREATE OR REPLACE FUNCTION audit_books()
RETURNS TRIGGER AS $func$
BEGIN
    INSERT INTO book_audit (book_id, action, changed_at, old_data)
    VALUES (COALESCE(NEW.id, OLD.id), TG_OP, now(), to_jsonb(OLD));
    RETURN NEW;
END;
$func$ LANGUAGE plpgsql;

CREATE TRIGGER books_audit
AFTER UPDATE OR DELETE ON books
FOR EACH ROW EXECUTE FUNCTION audit_books();

Use triggers carefully

They run invisibly, which makes debugging harder — someone reading the application code has no hint that a trigger fired. Reserve them for cross-cutting concerns (timestamps, audit trails) rather than business logic.