SQL and PL/pgSQL functions

Server-side functions keep data-heavy logic next to the data and are needed for triggers.

PostgreSQL functions can be written in several languages. The two built-in ones:

SQL functions (LANGUAGE sql) are one or more SQL statements; the result of the last is returned. Simple ones can be inlined into the calling query by the planner, so they're as fast as writing the SQL directly. Since PostgreSQL 14 you can write the body in standard form with BEGIN ATOMIC ... END.
PL/pgSQL (LANGUAGE plpgsql) adds variables (DECLARE), IF, loops, RAISE for errors and notices, and EXCEPTION blocks. Use it when you need procedural logic.

Key options:

Volatility: IMMUTABLE (same inputs, same output, no data access — required for expression indexes), STABLE (doesn't modify data, consistent within a statement), VOLATILE (default).
RETURNS TABLE (...) or RETURNS SETOF to return rows; call it in FROM.
SECURITY DEFINER runs with the owner's privileges — powerful, so also set search_path on the function.

Procedures (CREATE PROCEDURE, invoked with CALL) differ from functions in that they can COMMIT inside, which is useful for batch jobs that process data in chunks.

Keep business logic mostly in the application unless it's data-intensive or must be enforced for every writer; server code is harder to version, test and debug.

Example

CREATE TABLE account (id int PRIMARY KEY, balance numeric NOT NULL);
INSERT INTO account VALUES (1, 100), (2, 0);

CREATE FUNCTION transfer(src int, dst int, amount numeric)
RETURNS numeric LANGUAGE plpgsql AS $$
DECLARE
    left_over numeric;
BEGIN
    UPDATE account SET balance = balance - amount
    WHERE id = src RETURNING balance INTO left_over;
    IF left_over < 0 THEN
        RAISE EXCEPTION 'insufficient funds in account %', src;
    END IF;
    UPDATE account SET balance = balance + amount WHERE id = dst;
    RETURN left_over;
END $$;

SELECT transfer(1, 2, 30);    -- 70
SELECT transfer(1, 2, 500);   -- ERROR: insufficient funds in account 1
SELECT * FROM account ORDER BY id;   -- 1 | 70, 2 | 30

Remember

LANGUAGE sql for simple, inlinable functions; plpgsql for procedural logic
Mark volatility correctly; IMMUTABLE is needed for index expressions
Procedures can COMMIT inside; functions cannot

Watch out for

Declaring a function IMMUTABLE when it reads tables, leading to stale or wrong results.

Check yourself

What happens to the first UPDATE when RAISE EXCEPTION fires inside the function?

Official docs: www.postgresql.org/docs/current/plpgsql.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks