Functions, Procedures, and Triggers

Functions, procedures, and triggers run code inside the database. PostgreSQL’s built-in procedural language is PL/pgSQL. The examples work on one small table of accounts.

-- setup.sql
CREATE TABLE accounts (
    id         INT PRIMARY KEY,
    owner      TEXT NOT NULL,
    balance    NUMERIC(10, 2) NOT NULL,
    updated_at TIMESTAMPTZ
);
INSERT INTO accounts (id, owner, balance) VALUES (1, 'Ana', 100.00), (2, 'Ben', 20.00);

$ psql -d sqldemo -f setup.sql
CREATE TABLE
INSERT 0 2

A SQL Function

The simplest function is a single SQL expression with parameters.

-- sql_function.sql
CREATE FUNCTION with_tax(amount NUMERIC, rate NUMERIC DEFAULT 0.20)
RETURNS NUMERIC
LANGUAGE sql IMMUTABLE
RETURN round(amount * (1 + rate), 2);

SELECT with_tax(100) AS default_rate, with_tax(100, 0.05) AS reduced,
       with_tax(rate => 0.10, amount => 50) AS named_args;

$ psql -d sqldemo -f sql_function.sql
CREATE FUNCTION
 default_rate | reduced | named_args 
--------------+---------+------------
       120.00 |  105.00 |      55.00
(1 row)

DEFAULT 0.20 makes rate optional, and name => value passes arguments by name in any order. IMMUTABLE promises the same inputs always give the same output, which lets PostgreSQL use the function in index expressions and fold constant calls.

PL/pgSQL: Variables, IF, and RAISE NOTICE

PL/pgSQL adds variables and control flow. The body sits between $$ quotes.

-- plpgsql.sql
CREATE FUNCTION grade(score INT) RETURNS TEXT
LANGUAGE plpgsql AS $$
DECLARE
    result TEXT;
BEGIN
    IF score >= 90 THEN
        result := 'A';
    ELSIF score >= 75 THEN
        result := 'B';
    ELSE
        result := 'C';
    END IF;
    RAISE NOTICE 'score % -> %', score, result;
    RETURN result;
END;
$$;
SELECT grade(95) AS g1, grade(80) AS g2, grade(40) AS g3;

$ psql -d sqldemo -f plpgsql.sql
CREATE FUNCTION
psql:plpgsql.sql:18: NOTICE:  score 95 -> A
psql:plpgsql.sql:18: NOTICE:  score 80 -> B
psql:plpgsql.sql:18: NOTICE:  score 40 -> C
 g1 | g2 | g3 
----+----+----
 A  | B  | C
(1 row)

Each call printed a NOTICE before the result row appeared. % in a RAISE message is a placeholder filled from the arguments that follow. Notices are the print statement of PL/pgSQL: they never stop execution.

Loops and OUT Parameters

OUT parameters let a function return several named values without declaring a type.

-- loops_out.sql
CREATE FUNCTION stats(n INT, OUT total INT, OUT evens INT)
LANGUAGE plpgsql AS $$
BEGIN
    total := 0;
    evens := 0;
    FOR i IN 1..n LOOP
        total := total + i;
        IF i % 2 = 0 THEN
            evens := evens + 1;
        END IF;
    END LOOP;
END;
$$;
SELECT * FROM stats(10);

$ psql -d sqldemo -f loops_out.sql
CREATE FUNCTION
 total | evens 
-------+-------
    55 |     5
(1 row)

There is no RETURN value: whatever the OUT parameters hold at the end is the result. SELECT * FROM spreads them into columns. The loop is fine for a demonstration, but SELECT sum(i) FROM generate_series(1, 10) i does the same work as one set-based query, which is usually faster.

Raising Errors

RAISE EXCEPTION aborts the function and the statement that called it.

-- exceptions.sql
CREATE FUNCTION withdraw(acct INT, amt NUMERIC) RETURNS NUMERIC
LANGUAGE plpgsql AS $$
DECLARE
    new_balance NUMERIC;
BEGIN
    UPDATE accounts SET balance = balance - amt WHERE id = acct
    RETURNING balance INTO new_balance;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'account % does not exist', acct USING ERRCODE = 'no_data_found';
    END IF;
    IF new_balance < 0 THEN
        RAISE EXCEPTION 'insufficient funds: balance would be %', new_balance
            USING HINT = 'Withdraw at most the current balance.';
    END IF;
    RETURN new_balance;
END;
$$;
SELECT withdraw(1, 30) AS ana_left;
SELECT withdraw(2, 50);
SELECT withdraw(9, 5);
SELECT id, balance FROM accounts ORDER BY id;

$ psql -d sqldemo -f exceptions.sql
CREATE FUNCTION
 ana_left 
----------
    70.00
(1 row)

psql:exceptions.sql:20: ERROR:  insufficient funds: balance would be -30.00
HINT:  Withdraw at most the current balance.
CONTEXT:  PL/pgSQL function withdraw(integer,numeric) line 11 at RAISE
psql:exceptions.sql:21: ERROR:  account 9 does not exist
CONTEXT:  PL/pgSQL function withdraw(integer,numeric) line 8 at RAISE
 id | balance 
----+---------
  1 |   70.00
  2 |   20.00
(2 rows)

For Ben, the UPDATE had already run and set his balance to -30 before the check raised the exception. The final SELECT shows 20.00 anyway: the error rolled back everything the failed statement did, including work done inside the function. NOT FOUND is set by the previous SQL statement, which is how the function noticed account 9 does not exist.

Catching Errors

An EXCEPTION block handles an error inside the function instead of passing it up.

-- catch.sql
CREATE FUNCTION safe_ratio(a NUMERIC, b NUMERIC) RETURNS NUMERIC
LANGUAGE plpgsql AS $$
BEGIN
    RETURN round(a / b, 2);
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'division by zero, returning NULL';
        RETURN NULL;
END;
$$;
SELECT safe_ratio(10, 4) AS ok, safe_ratio(10, 0) AS caught;

$ psql -d sqldemo -f catch.sql
CREATE FUNCTION
psql:catch.sql:12: NOTICE:  division by zero, returning NULL
  ok  | caught 
------+--------
 2.50 |       
(1 row)

The second call divided by zero, the handler caught it, and the query still returned a row. Catch only errors you expect: a blanket WHEN OTHERS hides real bugs, and each EXCEPTION block costs a savepoint, which adds overhead in hot code.

RETURN QUERY: Functions That Return Rows

RETURNS TABLE plus RETURN QUERY makes a function usable like a parameterised view.

-- return_query.sql
CREATE FUNCTION rich_accounts(min_balance NUMERIC)
RETURNS TABLE (owner TEXT, balance NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
    SELECT a.owner, a.balance FROM accounts a
    WHERE a.balance >= min_balance ORDER BY a.balance DESC;
END;
$$;
SELECT * FROM rich_accounts(10);

$ psql -d sqldemo -f return_query.sql
CREATE FUNCTION
 owner | balance 
-------+---------
 Ana   |   70.00
 Ben   |   20.00
(2 rows)

The alias a is not decoration. owner and balance are also the names of the function’s output columns, which PL/pgSQL treats as variables; writing plain owner in the query would be ambiguous and raise an error.

Procedures and CALL

A procedure is run with CALL and may commit inside its body.

-- procedure.sql
CREATE PROCEDURE pay_interest(rate NUMERIC)
LANGUAGE plpgsql AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT id FROM accounts ORDER BY id LOOP
        UPDATE accounts SET balance = round(balance * (1 + rate), 2) WHERE id = r.id;
        COMMIT;   -- each account committed separately
    END LOOP;
END;
$$;
CALL pay_interest(0.10);
SELECT id, balance FROM accounts ORDER BY id;
SELECT pay_interest(0.10);

$ psql -d sqldemo -f procedure.sql
CREATE PROCEDURE
CALL
 id | balance 
----+---------
  1 |   77.00
  2 |   22.00
(2 rows)

psql:procedure.sql:15: ERROR:  pay_interest(numeric) is a procedure
LINE 1: SELECT pay_interest(0.10);
               ^
HINT:  To call a procedure, use CALL.

Both balances grew by 10%, and each account’s update was committed on its own, so a crash halfway through would keep the finished accounts. A function cannot do that, because it runs inside the caller’s transaction. Calling a procedure with SELECT is an error, and the hint says what to use instead.

Triggers

A trigger runs a function automatically when rows change. This BEFORE UPDATE trigger stamps updated_at.

-- trigger.sql
CREATE FUNCTION touch_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;
CREATE TRIGGER accounts_touch BEFORE UPDATE ON accounts
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
UPDATE accounts SET balance = balance + 1 WHERE id = 2;
SELECT id, updated_at IS NOT NULL AS touched FROM accounts ORDER BY id;

$ psql -d sqldemo -f trigger.sql
CREATE FUNCTION
CREATE TRIGGER
UPDATE 1
 id | touched 
----+---------
  1 | f
  2 | t
(2 rows)

NEW is the row about to be written. Changing it in a BEFORE trigger changes what gets stored, so only Ben, the row that was updated, got a timestamp. The query tests IS NOT NULL rather than printing the time, which would differ on every run.

An Audit Log Trigger

An AFTER trigger sees the final row, which suits audit logs. TG_OP says which operation fired it.

-- audit.sql
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY, op TEXT, row_id INT, old_row JSONB, new_row JSONB
);
CREATE FUNCTION audit_accounts() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO audit_log (op, row_id, old_row, new_row)
    VALUES (TG_OP, COALESCE(NEW.id, OLD.id),
            to_jsonb(OLD) - 'updated_at', to_jsonb(NEW) - 'updated_at');
    RETURN NULL;   -- return value ignored for AFTER triggers
END;
$$;
CREATE TRIGGER accounts_audit AFTER INSERT OR UPDATE OR DELETE ON accounts
FOR EACH ROW EXECUTE FUNCTION audit_accounts();
INSERT INTO accounts (id, owner, balance) VALUES (3, 'Cai', 5.00);
UPDATE accounts SET balance = 0 WHERE id = 3;
DELETE FROM accounts WHERE id = 3;
SELECT op, row_id, old_row, new_row FROM audit_log ORDER BY id;

$ psql -d sqldemo -f audit.sql
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
UPDATE 1
DELETE 1
   op   | row_id |                  old_row                   |                  new_row                   
--------+--------+--------------------------------------------+--------------------------------------------
 INSERT |      3 |                                            | {"id": 3, "owner": "Cai", "balance": 5.00}
 UPDATE |      3 | {"id": 3, "owner": "Cai", "balance": 5.00} | {"id": 3, "owner": "Cai", "balance": 0.00}
 DELETE |      3 | {"id": 3, "owner": "Cai", "balance": 0.00} | 
(3 rows)

OLD is empty for an insert and NEW is empty for a delete, which is why each row has one blank column. to_jsonb(row) captures the whole row without listing its columns, so the trigger keeps working when columns are added. updated_at is stripped only to keep the output stable here.

Practice

1. Write a SQL function initials(name TEXT) that returns the first letter, uppercased.

-- practice_initials.sql
CREATE FUNCTION initials(name TEXT) RETURNS TEXT
LANGUAGE sql IMMUTABLE
RETURN upper(left(name, 1));
SELECT owner, initials(owner) FROM accounts ORDER BY id;

$ psql -d sqldemo -f practice_initials.sql
CREATE FUNCTION
 owner | initials 
-------+----------
 Ana   | A
 Ben   | B
(2 rows)

2. Call withdraw inside a PL/pgSQL DO block and catch its insufficient-funds error, printing the message with RAISE NOTICE.

-- practice_do_catch.sql
DO $$
BEGIN
    PERFORM withdraw(2, 1000);
EXCEPTION
    WHEN raise_exception THEN
        RAISE NOTICE 'caught: %', SQLERRM;
END;
$$;
SELECT id, balance FROM accounts WHERE id = 2;

$ psql -d sqldemo -f practice_do_catch.sql
psql:practice_do_catch.sql:9: NOTICE:  caught: insufficient funds: balance would be -977.00
DO
 id | balance 
----+---------
  2 |   23.00
(1 row)

3. Add a BEFORE INSERT trigger that rejects negative opening balances.

-- practice_trigger_check.sql
CREATE FUNCTION no_negative_open() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.balance < 0 THEN
        RAISE EXCEPTION 'opening balance % is negative', NEW.balance;
    END IF;
    RETURN NEW;
END;
$$;
CREATE TRIGGER accounts_open BEFORE INSERT ON accounts
FOR EACH ROW EXECUTE FUNCTION no_negative_open();
INSERT INTO accounts (id, owner, balance) VALUES (4, 'Dee', -5);

$ psql -d sqldemo -f practice_trigger_check.sql
CREATE FUNCTION
CREATE TRIGGER
psql:practice_trigger_check.sql:13: ERROR:  opening balance -5.00 is negative
CONTEXT:  PL/pgSQL function no_negative_open() line 4 at RAISE