Functions and Procedures: Teaching the Database New Verbs

Packaging logic inside the database — a plain SQL function, a full PL/pgSQL function with variables and loops, and a procedure that can COMMIT where a function cannot — plus the volatility label that decides whether the planner trusts your function enough to index it. Run against PostgreSQL 18.

A trigger runs your code when the database decides. A function runs it when you decide, by name, wherever a value or a query can go. Functions are how you give the database new verbs: order_total(42), customer_tier(7). They live in the schema, every client can call them, and they run next to the data instead of shuttling rows to your application and back. This chapter builds three of them against the bookshop: a plain SQL function, a richer PL/pgSQL one, and a procedure — a close cousin with one power a function lacks. Then it explains the one-word label that decides whether the planner will let you build an index on your function at all.

The plain SQL function

The simplest function is a SQL query with a name and some parameters. Here is the total value of an order, straight from the line items:

CREATE FUNCTION order_total(p_order_id integer) RETURNS numeric AS $$
    SELECT COALESCE(sum(quantity * unit_price), 0)
    FROM order_items
    WHERE order_id = p_order_id;
$$ LANGUAGE sql STABLE;

LANGUAGE sql means the body is a SQL statement; the parameter p_order_id stands in for a value the caller supplies. Once it exists, it is a verb you can use anywhere an expression is allowed, including in the SELECT list of another query, once per row:

SELECT order_id, order_total(order_id) AS total
FROM orders
ORDER BY order_id
LIMIT 5;
 order_id | total
----------+--------
        1 | 179.99
        2 |  53.31
        3 | 188.58
        4 | 176.02
        5 |  30.03

That is the payoff of a function: the messy join-and-sum is named once and reads like a column everywhere else. (Ignore the STABLE word for now; it gets its own section, and it matters more than it looks.)

PL/pgSQL: variables, branches, loops

A SQL function is one statement. When you need real control flow — a variable, an IF, a loop — you switch languages to PL/pgSQL, Postgres’s procedural language. The body then lives between BEGIN and END, with a DECLARE block for locals. Here is a function that classifies a customer by lifetime spend:

CREATE FUNCTION customer_tier(p_customer_id integer) RETURNS text AS $$
DECLARE
    lifetime numeric;
BEGIN
    SELECT COALESCE(sum(oi.quantity * oi.unit_price), 0)
      INTO lifetime
      FROM orders o
      JOIN order_items oi ON oi.order_id = o.order_id
     WHERE o.customer_id = p_customer_id;

    IF lifetime >= 3000 THEN
        RETURN 'gold';
    ELSIF lifetime >= 1500 THEN
        RETURN 'silver';
    ELSE
        RETURN 'bronze';
    END IF;
END;
$$ LANGUAGE plpgsql STABLE;

Three things that are new versus a SQL function: DECLARE lifetime numeric is a local variable; SELECT ... INTO lifetime runs a query and stashes its single result in that variable; and IF / ELSIF / ELSE branches on it before RETURNing. Call it across the customer table:

SELECT customer_id, customer_tier(customer_id) AS tier
FROM customers ORDER BY customer_id LIMIT 6;
 customer_id |  tier
-------------+--------
           1 | bronze
           2 | silver
           3 | silver
           4 | bronze
           5 | bronze
           6 | bronze

Loops work too. A FOR loop over a numeric range reads exactly as you’d guess:

CREATE FUNCTION running_factorial(n integer) RETURNS bigint AS $$
DECLARE
    result bigint := 1;
    i integer;
BEGIN
    FOR i IN 2..n LOOP
        result := result * i;
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT running_factorial(5) AS fact_5, running_factorial(10) AS fact_10;
 fact_5 | fact_10
--------+---------
    120 | 3628800

You now have the whole toolkit: variables, assignment, IF, LOOP, and RETURN. PL/pgSQL has more (FOR row IN SELECT ... to iterate query results, WHILE, exception handling with BEGIN ... EXCEPTION), but this is the shape of all of it.

Procedures, and the one thing they can do

A procedure looks almost identical to a function, with two differences. You invoke it with CALL instead of putting it in a query, and it does not return a value into an expression. The reason procedures exist at all is a single capability: a procedure can COMMIT in the middle of its own body. A function cannot, ever. That difference is the whole point of the feature.

Why would you want it? For long batch work you don’t want to do in one giant transaction. Archive a million rows a thousand at a time, committing each batch, so a failure halfway doesn’t roll back everything already done. Here is the shape:

CREATE PROCEDURE archive_in_batches() LANGUAGE plpgsql AS $$
DECLARE
    b integer;
BEGIN
    FOR b IN 1..3 LOOP
        INSERT INTO scratch.batch_log (note) VALUES ('batch ' || b);
        COMMIT;   -- durable after each batch
    END LOOP;
END;
$$;

CALL archive_in_batches();
SELECT id, note FROM scratch.batch_log ORDER BY id;
 id |  note
----+---------
  1 | batch 1
  2 | batch 2
  3 | batch 3

Each iteration committed, so the three rows landed in three separate transactions. Now try the same COMMIT inside a function:

CREATE FUNCTION try_commit() RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO scratch.batch_log (note) VALUES ('from function');
    COMMIT;
END;
$$;

SELECT try_commit();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function try_commit() line 4 at COMMIT

invalid transaction termination. A function runs inside whatever transaction called it — it is part of a larger statement, and a statement cannot commit half of itself. A procedure called with CALL owns its transaction boundary and can. That is the entire functional difference between the two; everything else is the same language.

Volatility: the label the planner reads

Look back at the function definitions. Each ended in STABLE, IMMUTABLE, or nothing. That word is the function’s volatility, and it is a promise you make to the planner about how the function behaves:

  • IMMUTABLE — same inputs always give the same output, forever, with no reads of the database. lower('ABC') is immutable. The planner may evaluate it once and reuse the result, and it may build an index on it.
  • STABLE — same inputs give the same output within a single statement, but may read the database. order_total is stable: it reads order_items, which could differ between statements, but won’t change under one query.
  • VOLATILE — anything goes; may return a different value on every call. random() and now()-style clock reads are volatile. This is the default if you say nothing, which is the safe-but-slow choice.

Mislabeling is not cosmetic. The planner uses volatility to decide whether it can skip re-evaluating your function, and — the sharp edge — whether it can put your function in an index. A functional index only makes sense if the function’s output never changes for a given input. So Postgres flatly refuses to index anything not marked IMMUTABLE:

CREATE FUNCTION lower_email(t text) RETURNS text LANGUAGE sql IMMUTABLE AS $$ SELECT lower(t) $$;
CREATE INDEX ON customers (lower_email(email));   -- fine

CREATE FUNCTION vol_tag(t text) RETURNS text LANGUAGE sql VOLATILE AS $$ SELECT t || random()::text $$;
CREATE INDEX ON customers (vol_tag(email));
ERROR:  functions in index expression must be marked IMMUTABLE

The immutable function indexed without complaint; the volatile one was rejected outright. So volatility is not a formality you tack on at the end. Label a genuinely immutable function VOLATILE and you lose the ability to index it and force needless re-evaluation. Label a volatile one IMMUTABLE and you get wrong answers — the planner will cache a value that was supposed to change. Mark each function as strictly as is true, and no stricter.

One line on other languages

PL/pgSQL is the default procedural language, but not the only one. Postgres can run function bodies in PL/Python (plpython3u), PL/Perl, PL/Tcl, and more, once the extension is installed — handy when a function needs a library that SQL doesn’t have. The volatility rules and the function-versus-procedure split are the same whichever language the body is written in. MySQL and SQL Server have their own procedural dialects (SQL/PSM and T-SQL); SQLite has none, and pushes this logic back into the application.

Final thoughts

A function names logic and returns a value you can drop into any query; a procedure names logic you CALL, and its one superpower is committing mid-body for batch work. Reach for plain LANGUAGE sql when the body is a single query, and PL/pgSQL when you need variables, branches, or loops. Then set the volatility honestly: IMMUTABLE, STABLE, or VOLATILE. That one word decides whether the planner trusts your function enough to fold it, skip it, or index it. Logic in the database is powerful precisely because every client inherits it; the next chapter is about changing the schema those clients depend on without breaking them.

Next: Change the schema without fear: migrations — forward and rollback scripts, transactional DDL, and the expand/contract pattern for zero-downtime changes.

Comments