Functions and procedures

Article.

Postgres will run your code. The question an interviewer is really asking is whether you know what the planner does with it — because a function is not a black box you can ignore, and the two ways of getting that wrong are a query that is a hundred times slower than it looks and an index that returns answers that were true last week.

Function or procedure

Both are callable server-side code. One distinction separates them, and it is transaction control.

CREATE FUNCTION order_total(oid bigint) RETURNS numeric
LANGUAGE sql STABLE AS $$
    SELECT sum(qty * unit_price) FROM order_line WHERE order_id = oid;
$$;

SELECT order_total(42);          -- returns a value, usable in a query

A function returns a value, is called inside an expression, and runs within the caller’s transaction. It cannot COMMIT, because there is no transaction of its own to commit.

Procedures own their transactions

A procedure (Postgres 11 and later) is invoked with CALL, returns nothing by default, and can commit mid-body:

CREATE PROCEDURE expire_sessions() LANGUAGE plpgsql AS $$
BEGIN
    LOOP
        DELETE FROM session
        WHERE id IN (SELECT id FROM session WHERE expires_at < now() LIMIT 5000);

        EXIT WHEN NOT FOUND;
        COMMIT;                  -- a function cannot do this
    END LOOP;
END $$;

CALL expire_sessions();

That is the whole reason procedures exist. Deleting ten million expired rows in one statement holds a transaction open for the duration: it pins the oldest transaction id, so VACUUM cannot reclaim anything newer anywhere in the database, and the WAL for the whole delete must be retained. Chunking with a commit between batches bounds both. You cannot write that as a function.

What the planner does with your function

This is the mechanism that decides whether server-side code is fast, and it has two halves: volatility and inlining.

Volatility

Every function declares how much the planner may assume:

VOLATILE is the default, and that default is a performance trap. An unmarked function in a WHERE clause is re-evaluated for every row examined, because the planner has been told it might return something different each time. Marking a genuinely stable lookup as STABLE lets the planner evaluate it once and use the result to drive an index scan.

The rule of thumb: a function that reads tables is STABLE; a pure computation on its arguments is IMMUTABLE; anything that writes, or calls now(), random() or a sequence, is VOLATILE.

Inlining, and why LANGUAGE sql is not a style choice

A simple LANGUAGE sql function — a single SELECT, not volatile — can be inlined: the planner substitutes its body into the calling query and plans the whole thing as one statement. Predicates push through it, indexes get used, the function effectively disappears.

A LANGUAGE plpgsql function is never inlined. It is a black box the planner calls, with a fixed cost guess and no visibility inside.

-- Inlinable. The planner sees through it to the index on order_line.order_id.
CREATE FUNCTION line_count(oid bigint) RETURNS bigint
LANGUAGE sql STABLE AS $$
    SELECT count(*) FROM order_line WHERE order_id = oid;
$$;

-- Opaque. Same result, but the planner cannot see the query inside.
CREATE FUNCTION line_count_pl(oid bigint) RETURNS bigint
LANGUAGE plpgsql STABLE AS $$
DECLARE n bigint;
BEGIN
    SELECT count(*) INTO n FROM order_line WHERE order_id = oid;
    RETURN n;
END $$;

Both return the same number. In a query that calls one per row over a large table, the first can be folded into a join the planner optimises as a whole, and the second is a per-row function call it cannot reason about at all.

Write it in sql unless you need control flow. plpgsql earns its place when you need loops, exception handling or variables — not as a default.

The IMMUTABLE lie

The dangerous mistake, and the one worth being able to demonstrate. IMMUTABLE is a promise the database believes without checking, and it is what allows a function’s result to be indexed.

Here is a function that lies:

CREATE FUNCTION customer_tier(cid bigint) RETURNS text
LANGUAGE sql IMMUTABLE AS $$          -- lie: it reads a table
    SELECT tier FROM customer WHERE id = cid;
$$;

CREATE INDEX idx_order_tier ON orders (customer_tier(customer_id));

Postgres accepts both. It has no way to verify the promise.

Watch it produce two different answers

INSERT INTO customer (id, tier) VALUES (1, 'gold');
INSERT INTO orders (id, customer_id) VALUES (100, 1);

UPDATE customer SET tier = 'bronze' WHERE id = 1;   -- the index is now stale

The index still holds gold for order 100, because nothing told it to change — an update to customer does not invalidate an index on orders.

Now the same query, twice, with only the plan varying:

SET enable_indexscan = on;
SELECT id FROM orders WHERE customer_tier(customer_id) = 'gold';
--  id
-- -----
-- 100          <- read from the stale index

SET enable_indexscan = off;
SELECT id FROM orders WHERE customer_tier(customer_id) = 'gold';
--  id
-- ----
-- (0 rows)     <- recomputed, correct

One query, one dataset, two answers. Nothing errors. The bug surfaces when the planner’s choice changes — after an ANALYZE, as the table grows, or on a replica with different statistics — so it presents as “the report was wrong last Tuesday” and is nearly impossible to reproduce on demand.

The fix is to mark it STABLE and drop the index, because a STABLE function cannot be indexed and that restriction is the point: if the result depends on data that can change, it is not indexable.

Where else it bites

SECURITY DEFINER with a mutable search_path. A function marked SECURITY DEFINER runs with the privileges of its owner. If it calls an unqualified name, the caller controls resolution through their search_path — so a caller creates their own public.customer and your privileged function reads it instead.

CREATE FUNCTION approve(oid bigint) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = pg_catalog, pg_temp     -- pin it; this is not optional
AS $$ ... $$;

Every SECURITY DEFINER function needs that SET search_path. Omitting it is a privilege-escalation vector, not a style issue.

Cost estimates. Functions default to a cost of 100, which the planner uses to order filter evaluation. An expensive function — one that calls out, or scans — declared at the default may be evaluated before a cheap integer comparison that would have eliminated the row. COST 10000 on the expensive one tells the planner to try it last.

For set-returning functions, ROWS is the equivalent hint about how many rows to expect, and a bad estimate produces a bad join order.

Migration and review. A function lives in the database, not in the diff. Ship it as a migration, never psql by hand, and remember that CREATE OR REPLACE FUNCTION takes a lock on dependent objects — replacing a function used by a busy query is not free.

The trade not taken

Almost all business logic should stay in the application, and the reasons are about people rather than performance: it is version controlled with its tests, it appears in a stack trace, it is reviewed by the team that owns the domain, and it can be debugged with a debugger.

Server-side code earns its place in exactly two cases. Enforcement, where the invariant must hold regardless of which client connects — that is usually a constraint or a trigger, see Triggers in practice. And data gravity, where moving computation to the data avoids shipping millions of rows across the network to compute one number.

An aggregate over ten million rows belongs in SQL. A decision about whether to refund a customer does not, however tempting it is that the data is right there.

Related

• Triggers in practice - the other half of server-side logic
• Reading a query plan - seeing whether your function was inlined
• VACUUM, autovacuum, table bloat, and transaction wraparound - why a long transaction blocks reclamation