Triggers & PL/pgSQL

PostgreSQL lets you write functions and procedures in PL/pgSQL — a procedural language with variables, loops, conditionals, and exception handling. Combined with triggers, you can enforce rules and automate behaviour right inside the database.

Functions

SQL vs PL/pgSQL

PostgreSQL supports multiple function languages. The two most common:

Language	When to use
`LANGUAGE sql`	Function body is a single query — fast, inlinable by the planner.
`LANGUAGE plpgsql`	You need control flow, variables, exceptions, multiple statements.

A simple SQL function

CREATE OR REPLACE FUNCTION full_name(first text, last text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT first || ' ' || last;
$$;

SELECT full_name('Ada', 'Lovelace');  -- 'Ada Lovelace'

IMMUTABLE tells the planner the result depends only on the inputs — it can be cached or inlined.

A simple PL/pgSQL function

CREATE OR REPLACE FUNCTION raise_salary(emp_id int, pct numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
    new_salary numeric;
BEGIN
    UPDATE employees
       SET salary = salary * (1 + pct / 100.0)
     WHERE id = emp_id
    RETURNING salary INTO new_salary;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Employee % not found', emp_id;
    END IF;

    RETURN new_salary;
END;
$$;

SELECT raise_salary(1, 5);  -- 5% raise for employee 1, returns new salary

PL/pgSQL Anatomy

CREATE OR REPLACE FUNCTION name(arg1 type, arg2 type)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
    -- variable declarations
    v_count int := 0;
    v_row   employees%ROWTYPE;
BEGIN
    -- statements
    SELECT * INTO v_row FROM employees WHERE id = arg1;

    IF v_row.salary IS NULL THEN
        RAISE NOTICE 'No salary on file';
    END IF;

    RETURN v_row.salary;

EXCEPTION
    WHEN no_data_found THEN
        RETURN 0;
    WHEN OTHERS THEN
        RAISE;
END;
$$;

Variables

DECLARE
    counter   int := 0;            -- typed scalar
    emp       employees%ROWTYPE;   -- whole row of a table
    salary    employees.salary%TYPE; -- same type as a column
    names     text[];              -- array

Control Flow

-- IF / ELSIF / ELSE
IF salary > 100000 THEN
    RAISE NOTICE 'Senior';
ELSIF salary > 60000 THEN
    RAISE NOTICE 'Mid';
ELSE
    RAISE NOTICE 'Junior';
END IF;

-- CASE
CASE bucket
    WHEN 'A' THEN ... ;
    WHEN 'B', 'C' THEN ... ;
    ELSE ... ;
END CASE;

-- Simple LOOP with EXIT
LOOP
    counter := counter + 1;
    EXIT WHEN counter >= 10;
END LOOP;

-- WHILE
WHILE counter < 10 LOOP
    counter := counter + 1;
END LOOP;

-- Numeric FOR
FOR i IN 1..5 LOOP
    RAISE NOTICE 'i=%', i;
END LOOP;

-- FOR over a query
FOR rec IN SELECT id, name FROM employees ORDER BY id LOOP
    RAISE NOTICE '% -> %', rec.id, rec.name;
END LOOP;

Returning Sets

`RETURNS TABLE` with `RETURN QUERY`

CREATE OR REPLACE FUNCTION top_earners(n int)
RETURNS TABLE(id int, name text, salary numeric)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT e.id, e.name, e.salary
        FROM employees e
        ORDER BY e.salary DESC
        LIMIT n;
END;
$$;

SELECT * FROM top_earners(3);

`SETOF` with `RETURN NEXT`

CREATE OR REPLACE FUNCTION ids_above(threshold numeric)
RETURNS SETOF int
LANGUAGE plpgsql
AS $$
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT id FROM employees WHERE salary > threshold LOOP
        RETURN NEXT rec.id;
    END LOOP;
    RETURN;
END;
$$;

Exception Handling

BEGIN
    INSERT INTO accounts(id, balance) VALUES (1, 100);
EXCEPTION
    WHEN unique_violation THEN
        UPDATE accounts SET balance = 100 WHERE id = 1;
    WHEN division_by_zero THEN
        RAISE NOTICE 'Cannot divide by zero';
    WHEN OTHERS THEN
        RAISE WARNING 'Unexpected error: %', SQLERRM;
        RAISE;  -- re-raise
END;

RAISE levels: DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION (aborts).

Stored Procedures (PG 11+)

Procedures are like functions but can manage transactions (COMMIT, ROLLBACK inside the body) and don't return a value.

CREATE OR REPLACE PROCEDURE bulk_promote(min_years int)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
       SET salary = salary * 1.10
     WHERE EXTRACT(YEAR FROM age(hire_date)) >= min_years;

    COMMIT;
END;
$$;

CALL bulk_promote(5);

Function	Procedure
Returns a value	No return (only `OUT` params)
Called as `SELECT fn(...)`	Called as `CALL proc(...)`
Cannot manage transactions	Can `COMMIT` / `ROLLBACK`
Usable inside queries	Standalone only

Triggers

A trigger runs a function automatically before or after INSERT, UPDATE, DELETE, or TRUNCATE on a table.

Trigger function

A trigger function returns TRIGGER and accesses these special variables:

Variable	Meaning
`NEW`	Row being inserted/updated (NULL for DELETE)
`OLD`	Row before update or being deleted (NULL for INSERT)
`TG_OP`	Operation: `'INSERT'`, `'UPDATE'`, `'DELETE'`, `'TRUNCATE'`
`TG_TABLE_NAME`	Name of the table
`TG_WHEN`	`'BEFORE'`, `'AFTER'`, `'INSTEAD OF'`

Example 1 — Auto-update `updated_at`

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

CREATE TRIGGER tr_employees_updated_at
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Now any UPDATE employees SET … automatically refreshes updated_at.

Example 2 — Audit log

CREATE TABLE employees_audit (
    audit_id    bigserial PRIMARY KEY,
    op          char(1),                -- 'I','U','D'
    changed_at  timestamptz DEFAULT NOW(),
    changed_by  text DEFAULT current_user,
    emp_id      int,
    before_data jsonb,
    after_data  jsonb
);

CREATE OR REPLACE FUNCTION audit_employees()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO employees_audit(op, emp_id, after_data)
        VALUES ('I', NEW.id, to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO employees_audit(op, emp_id, before_data, after_data)
        VALUES ('U', NEW.id, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO employees_audit(op, emp_id, before_data)
        VALUES ('D', OLD.id, to_jsonb(OLD));
        RETURN OLD;
    END IF;
END;
$$;

CREATE TRIGGER tr_employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION audit_employees();

Every change to employees is now logged with full before/after JSON snapshots.

Example 3 — Enforce a business rule

CREATE OR REPLACE FUNCTION check_salary_cap()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.salary > 250000 THEN
        RAISE EXCEPTION 'Salary % exceeds company cap', NEW.salary;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER tr_salary_cap
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION check_salary_cap();

BEFORE vs AFTER vs INSTEAD OF

Timing	Use case
`BEFORE` row trigger	Modify `NEW` before write, reject with exception, fill defaults
`AFTER` row trigger	Side effects: logging, cascading updates, notifications
`INSTEAD OF` (views only)	Replace the action — translate writes on a view to base tables
Statement-level (`FOR EACH STATEMENT`)	Once per statement instead of once per row, can use transition tables

Transition Tables (PG 10+)

A statement-level trigger can see all affected rows at once via REFERENCING:

CREATE OR REPLACE FUNCTION log_bulk_update()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO change_log(emp_id, before_salary, after_salary)
    SELECT n.id, o.salary, n.salary
    FROM new_emp n JOIN old_emp o ON o.id = n.id
    WHERE n.salary IS DISTINCT FROM o.salary;
    RETURN NULL;
END;
$$;

CREATE TRIGGER tr_bulk_log
AFTER UPDATE ON employees
REFERENCING NEW TABLE AS new_emp OLD TABLE AS old_emp
FOR EACH STATEMENT EXECUTE FUNCTION log_bulk_update();

Far cheaper than firing the trigger N times for an N-row update.

Function Volatility — IMMUTABLE / STABLE / VOLATILE

This category tells the planner whether the result is reproducible:

Marker	Meaning	Examples
`IMMUTABLE`	Same input → same output, ever	`length(text)`, pure math
`STABLE`	Same within one statement, may change between queries	`now()`, lookups
`VOLATILE` (default)	May change every call	`random()`, `nextval()`, anything that writes

Marking correctly enables index use on expression indexes:

CREATE INDEX idx_lower_name ON employees ((lower(name)));
-- Works only because lower() is IMMUTABLE.

Permissions

GRANT EXECUTE ON FUNCTION top_earners(int) TO reporting_role;

-- Run with the function owner's privileges (use carefully — security risk if logic is sloppy)
CREATE FUNCTION ... SECURITY DEFINER ...;

⚠

SECURITY DEFINER functions run with the privileges of the user who created them. Always set search_path explicitly inside them (SET search_path = pg_catalog, public) to prevent search-path injection attacks.

Common Pitfalls

Recursion — a trigger that updates the same table can fire itself. Guard with IF NEW IS DISTINCT FROM OLD or use pg_trigger_depth().
Trigger order — triggers on the same event fire alphabetically by name. Prefix names (01_, 02_) when order matters.
Performance — row-level triggers on large bulk operations can be 10–100× slower than statement triggers with transition tables.
Returning the right value — BEFORE row triggers must return NEW (or modified NEW); returning NULL skips the row. AFTER triggers return value is ignored but the convention is to return NEW/OLD.

Inspecting

-- List functions
\df

-- Show function source
\sf top_earners

-- List triggers on a table
\d employees

-- All triggers
SELECT event_object_table, trigger_name, action_timing, event_manipulation
FROM information_schema.triggers
ORDER BY event_object_table;

Summary

Use LANGUAGE sql for one-statement functions, plpgsql when you need control flow.
PL/pgSQL gives you IF, loops, exceptions, RAISE, and row variables.
Functions return values; procedures (PG 11+) can COMMIT/ROLLBACK and are called with CALL.
A trigger pairs with a trigger function (RETURNS TRIGGER) and reads NEW, OLD, TG_OP.
Common trigger patterns: updated_at stamp, audit log, business-rule enforcement, denormalized counts.
Mark immutable functions IMMUTABLE so they can be used in expression indexes.
Prefer statement-level triggers with transition tables for bulk performance.

Triggers & PL/pgSQL

Functions

SQL vs PL/pgSQL

A simple SQL function

A simple PL/pgSQL function

PL/pgSQL Anatomy

Variables

Control Flow

Returning Sets

RETURNS TABLE with RETURN QUERY

SETOF with RETURN NEXT

Exception Handling

Stored Procedures (PG 11+)

Triggers

Trigger function

Example 1 — Auto-update updated_at

Example 2 — Audit log

Example 3 — Enforce a business rule

BEFORE vs AFTER vs INSTEAD OF

Transition Tables (PG 10+)

Function Volatility — IMMUTABLE / STABLE / VOLATILE

Permissions

Common Pitfalls

Inspecting

Summary

`RETURNS TABLE` with `RETURN QUERY`

`SETOF` with `RETURN NEXT`

Example 1 — Auto-update `updated_at`