Functions and procedures

A function is a named piece of SQL

A function takes arguments, runs some SQL or procedural code, and returns a value or a set of rows. The simplest kind is written in SQL itself:

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

SELECT id, order_total(id) FROM orders WHERE customer_id = 1;

For loops, conditions and error handling there is PL/pgSQL, PostgreSQL’s procedural language. You will see it in triggers on the next page.

Volatility: a promise PostgreSQL believes

Every function is labelled with how predictable it is:

The label is not decoration. PostgreSQL uses it to decide what it may evaluate once instead of per row, and what it may store in an index. Only IMMUTABLE functions can be indexed, because an index stores results computed once and reuses them forever:

CREATE OR REPLACE FUNCTION order_month(ts timestamptz) RETURNS timestamptz
LANGUAGE sql STABLE AS $$ SELECT date_trunc('month', ts) $$;

CREATE INDEX ON orders (order_month(ordered_at));

ERROR:  functions in index expression must be marked IMMUTABLE

The tempting fix is to change STABLE to IMMUTABLE. PostgreSQL does not check the claim.

       how          | count
--------------------+-------
 using the index    |     0
 scanning the table |     3

Procedures: when you need to commit along the way

A function runs inside the transaction of the statement that called it, so it cannot end that transaction:

CREATE OR REPLACE FUNCTION f_commit() RETURNS void LANGUAGE plpgsql AS $$ BEGIN COMMIT; END $$;
SELECT f_commit();

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function f_commit() line 1 at COMMIT

A procedure, run with CALL, may COMMIT and ROLLBACK as it goes. That is what long batch jobs need, such as archiving old rows in chunks of 10,000 and committing after each chunk, so each chunk’s work is saved and its locks are released before the next one starts.

CREATE OR REPLACE PROCEDURE p_commit() LANGUAGE plpgsql AS $$ BEGIN COMMIT; END $$;
CALL p_commit();   -- CALL

Functions that run with someone else’s privileges

By default a function runs with the privileges of whoever calls it. SECURITY DEFINER makes it run with its owner’s privileges instead, which is how you can let a user do one specific privileged thing. It is also a classic security hole: if the function’s search_path includes a schema the caller can write to, the caller can create a table or function with a matching name and hijack it. The documentation’s rule: set search_path on every SECURITY DEFINER function, excluding schemas untrusted users can write to.

Check yourself

1. Why does PostgreSQL refuse to index a STABLE function?

   Show answer

   An index stores each result once and reuses it forever. A STABLE function only promises the same result within one statement, so a stored result could become wrong later.

   I could explain this without looking

2. What went wrong when order_month was marked IMMUTABLE?

   Show answer

   Its result depends on the session time zone. The index stored values computed in one time zone; a query in another time zone computed different values, so the index matched nothing while a table scan found three rows.

   I could explain this without looking

3. When do you need a procedure instead of a function?

   Show answer

   When the code must commit or roll back partway through, as in a batch job that processes rows in chunks. Only procedures called with CALL can end and start transactions.

   I could explain this without looking