7.2.8 — Stored Procedures and Functions

Checkout needs to reserve stock. The rule is simple to say: if there are enough units, take them; if not, refuse. Written in application code it looks like this.

const row = await db.query('SELECT qty FROM stock WHERE sku = $1', [sku]);   // (1)
if (row.qty < wanted) throw new Error('out of stock');                        // (2)
await db.query('UPDATE stock SET qty = qty - $1 WHERE sku = $2',
               [wanted, sku]);                                                // (3)
await db.query('INSERT INTO reservations (sku, qty, order_id) VALUES ($1,$2,$3)',
               [sku, wanted, orderId]);                                       // (4)

(1) Read the stock. (2) Decide in the application. (3) Take the units. (4) Record the reservation. Four round trips, and — much worse — between line 1 and line 3 another request can read the same number and decide the same thing. Both pass the check, both subtract, and you have sold five units of a product you had three of. Chapter 7.4.1 names this a lost update.

A stored procedure is code that lives inside the database and runs there. This whole sequence becomes one call, one round trip, one transaction, with the check and the write inseparable.

CREATE PROCEDURE reserve_stock(p_sku text, p_qty int, p_order_id bigint)
LANGUAGE plpgsql AS $$
DECLARE
  available int;                                                   -- (1)
BEGIN
  SELECT qty INTO available FROM stock
   WHERE sku = p_sku FOR UPDATE;                                   -- (2)

  IF NOT FOUND THEN                                                -- (3)
    RAISE EXCEPTION 'unknown sku %', p_sku USING ERRCODE = 'P0001';
  END IF;

  IF available < p_qty THEN                                        -- (4)
    RAISE EXCEPTION 'only % left for %', available, p_sku
      USING ERRCODE = 'P0002';
  END IF;

  UPDATE stock SET qty = qty - p_qty WHERE sku = p_sku;            -- (5)
  INSERT INTO reservations (sku, qty, order_id)
       VALUES (p_sku, p_qty, p_order_id);                          -- (6)
END;
$$;

(1) DECLARE introduces local variables, each with a type. (2) SELECT … INTO puts one column of one row into a variable, and FOR UPDATE locks that stock row until this transaction ends, so a second checkout for the same SKU waits here rather than reading a number that is about to change. That single clause is what removes the race. (3) FOUND is a variable PL/pgSQL sets after every query, saying whether it touched any row. Without this check, an unknown SKU would leave available as null and every comparison after it would be unknown rather than false — the three-valued logic from Chapter 7.1 section 3, arriving in procedural code. (4) The business rule, now inside the same transaction as the read that informed it. (5) and (6) The two writes. If either fails, the whole call rolls back and the stock is untouched.

Calling it:

CALL reserve_stock('SKU-9', 2, 1001);

One round trip instead of four, and the check-then-write gap is closed. Those are the two things procedures genuinely buy, and the rest of this page is about when they are worth their cost.

1. Function or procedure — the difference is transactions

Both are code stored in the database. The distinction confuses people because it changed, and because every engine draws the line slightly differently.

A function returns a value and runs inside whatever transaction called it. It cannot commit, because it is a step in somebody else's statement — committing halfway through a SELECT would make no sense. You call it by using it in an expression.

A procedure does not have to return anything and may control transactions itself. You call it with CALL. PostgreSQL added procedures in version 11 specifically to allow COMMIT and ROLLBACK inside stored code, which functions could never do.

	Function	Procedure
Called with	Inside a query	CALL
Returns	A value or a set	Nothing, or output parameters
Can COMMIT	No	Yes
Usable in SELECT	Yes	No

SELECT id, order_total(id) FROM orders;   -- a function, once per row
CALL reserve_stock('SKU-9', 2, 1001);     -- a procedure

The COMMIT ability is the reason procedures exist, and it has one real use: a long batch that must be committed in chunks so it does not hold one enormous transaction. That connects straight to Chapter 7.2.7 — a single transaction running for an hour holds a snapshot and stops vacuum working.

CREATE PROCEDURE archive_old_orders()
LANGUAGE plpgsql AS $$
DECLARE moved int;
BEGIN
  LOOP
    WITH batch AS (
      DELETE FROM orders WHERE placed_at < now() - interval '5 years'
      RETURNING *                                              -- (1)
    )
    INSERT INTO orders_archive SELECT * FROM batch;            -- (2)

    GET DIAGNOSTICS moved = ROW_COUNT;                         -- (3)
    EXIT WHEN moved = 0;                                       -- (4)
    COMMIT;                                                    -- (5)
  END LOOP;
END;
$$;

(1) DELETE … RETURNING gives back the rows it removed, so the delete and the read are one pass over the data rather than two. (2) Those rows go straight into the archive table. Delete and insert are in one statement, so they cannot half-happen. (3) GET DIAGNOSTICS … ROW_COUNT reads how many rows the last statement affected. (4) Zero rows means the work is done. (5) The commit that only a procedure may write. Each round through the loop is its own transaction, so locks are released and vacuum can keep up. In a function this line is an error.

One catch worth knowing before it surprises you: a procedure can only commit if it was not called from inside an existing transaction block. CALL archive_old_orders(); on its own works. BEGIN; CALL archive_old_orders(); COMMIT; fails with invalid transaction termination, because the caller already owns the transaction.

Other engines draw the line differently, and the vocabulary follows the engine rather than the standard. SQL Server has stored procedures with OUTPUT parameters and functions that are restricted from changing data at all. MySQL has both, with procedures called by CALL and functions usable in expressions. Oracle groups related procedures and functions into a package, which is a named collection with a public interface and a private body — the nearest thing in this world to a module.

2. Writing a function: the shapes it can return

A scalar function returns one value and is the common case.

CREATE FUNCTION order_total(p_order_id bigint) RETURNS bigint
LANGUAGE sql STABLE AS $$                                        -- (1)
  SELECT COALESCE(SUM(qty * unit_price_minor), 0)                -- (2)
  FROM order_lines WHERE order_id = p_order_id;
$$;

(1) LANGUAGE sql — the body is plain SQL with no procedural code, which matters for speed and is explained in section 4. STABLE is a promise covered in section 3. (2) COALESCE because SUM over no rows is NULL, not zero — the trap from Chapter 7.2.2, and inside a function it is worse, because a null total will flow silently into whatever called it.

A set-returning function returns many rows and is used in FROM like a table.

CREATE FUNCTION recent_orders(p_customer_id bigint, p_limit int)
RETURNS TABLE (id bigint, placed_at timestamptz, total_minor bigint)   -- (1)
LANGUAGE sql STABLE AS $$
  SELECT o.id, o.placed_at, o.total_minor
  FROM orders o WHERE o.customer_id = p_customer_id
  ORDER BY o.placed_at DESC LIMIT p_limit;
$$;

SELECT * FROM recent_orders(42, 5);                                    -- (2)

(1) RETURNS TABLE (…) names and types the output columns, so callers get a proper row shape rather than an opaque record. RETURNS SETOF orders is the alternative when the output is exactly the shape of an existing table. (2) It is used exactly where a table would go, which means you can join to it.

Output parameters are the third shape, and they are how you return several values without inventing a type:

CREATE FUNCTION split_total(p_order_id bigint,
                           OUT net_minor bigint, OUT tax_minor bigint)
LANGUAGE plpgsql STABLE AS $$
BEGIN
  SELECT SUM(qty * unit_price_minor), SUM(qty * unit_price_minor) / 5
    INTO net_minor, tax_minor
  FROM order_lines WHERE order_id = p_order_id;
END;
$$;

SELECT * FROM split_total(1001);    -- two columns: net_minor, tax_minor

3. Volatility: the promise that decides what the planner may do

Every PostgreSQL function carries one of three labels, and choosing wrongly is the most common real bug in stored code — not because the function misbehaves, but because the planner believes you.

VOLATILE — the default. "This can return a different answer on every call, and it may change data." random(), now() in some senses, anything that writes. The planner must call it once per row and may not move it anywhere.

STABLE — "within a single statement, the same inputs give the same answer, and I do not change data." Anything reading tables is stable: the data could change between statements, but not during one, because the statement has a fixed snapshot. This is the right label for almost every read-only function you write, and mislabelling it VOLATILE is why a function in a WHERE clause forces a sequential scan — the planner cannot use it in an index condition, so it must evaluate it per row after fetching.

IMMUTABLE — "the same inputs always give the same answer, forever, for anybody." lower(text), arithmetic, length(). This is the strongest promise and it unlocks two things: the planner may compute the value once at planning time rather than per row, and you may build an index on it.

CREATE FUNCTION normalise_email(t text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$ SELECT lower(trim(t)); $$;   -- (1)

CREATE INDEX customers_email_idx ON customers (normalise_email(email));  -- (2)

(1) Trimming and lowercasing depend on nothing but the input, so the promise is true. (2) This index only exists because of the IMMUTABLE label. Try it with STABLE and PostgreSQL refuses: functions in index expression must be marked IMMUTABLE. The reason is blunt — the index stores computed values, so if the function's answer ever changed, the index would be silently wrong with no way to detect it.

And that is the danger. Nothing checks your promise. Mark a function IMMUTABLE when it reads a table, index it, then change the table, and the index now holds answers that no longer match the function. Queries return wrong rows, EXPLAIN looks perfect, and nothing anywhere reports an error. A false IMMUTABLE is one of the very few ways to make a PostgreSQL database quietly return incorrect results.

The common trap is date handling. to_char(timestamptz, 'YYYY-MM-DD') is not immutable, because converting an instant to a date depends on the session's time zone, which can differ between sessions. to_char(timestamp, …) on a zone-less timestamp is immutable. The distinction is invisible until an index built in one time zone is used from another.

4. What it costs to run, and the two performance surprises

A plain LANGUAGE sql function containing a single statement can be inlined, meaning the planner substitutes the function's body into the calling query and then optimises the whole thing together — the same trick as a view in Chapter 7.2.6. That is why order_total above is written in sql rather than plpgsql. Inlining needs the function to be STABLE or IMMUTABLE, to be one SELECT, and to not be SECURITY DEFINER. When it is inlined the function is free. When it is not, it is a separate call per row, and a per-row function call inside a query over a million rows is a million calls.

The difference is visible in a plan. An inlined function shows its body's operations as ordinary nodes. A non-inlined one appears as a Function Scan or simply as a filter you cannot see inside.

PL/pgSQL caches query plans, which brings back the generic-plan problem from Chapter 7.2.5 section 8. Each SQL statement inside a PL/pgSQL function is prepared the first time it runs and then reused for the life of the session. After about five executions PostgreSQL will consider switching to a generic plan that ignores the actual parameter values. On a skewed column that is the same trap as a prepared statement: excellent for the average input, terrible for the one big customer. The workaround inside a function is to build the statement text and run it with EXECUTE, which forces a fresh plan:

EXECUTE format('SELECT count(*) FROM orders WHERE tenant_id = %L', p_tenant)
  INTO n;                                                        -- (1)

(1) format with %L quotes the value safely as a literal. Use %L or %I (for an identifier), never string concatenation — building SQL by pasting a parameter into text inside the database is SQL injection with extra privileges, and Chapter 8.5.1 covers what that gets an attacker.

Exception blocks are more expensive than they look. Every BEGIN … EXCEPTION WHEN … END in PL/pgSQL creates a subtransaction, because the engine must be able to undo whatever the block did without losing the outer transaction. One of those is cheap. One inside a loop running a hundred thousand times is a hundred thousand subtransactions, which is a real and well-known way to make a batch job crawl. Handle the error outside the loop, or avoid the exception entirely — INSERT … ON CONFLICT DO NOTHING does the job of catching a unique violation without any subtransaction at all.

5. SECURITY DEFINER, and the attack you must know about

By default a function runs with the privileges of whoever calls it. SECURITY DEFINER makes it run with the privileges of the user who created it, which is the same idea as the view ownership rule in Chapter 7.2.6, and it is how you give somebody a narrow, controlled ability without giving them the table.

CREATE FUNCTION redeem_voucher(p_code text) RETURNS bigint
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp                     -- (1)
AS $$ … $$;

REVOKE ALL ON vouchers FROM app_user;                      -- (2)
GRANT EXECUTE ON FUNCTION redeem_voucher(text) TO app_user;

(1) This line is not optional and leaving it out is a privilege-escalation vulnerability. Here is the attack, because it is worth understanding rather than memorising.

The search path is the list of schemas PostgreSQL looks through, in order, when you write an unqualified name like vouchers. If the function body says UPDATE vouchers … and the search path is whatever the caller set, then a caller who can create objects in any schema on that path can create their own table or function called vouchers earlier in the path. Your SECURITY DEFINER function then operates on the attacker's object, with the owner's privileges. The same works for operators and functions used inside the body.

Pinning search_path to pg_catalog, pg_temp at definition time closes it: the caller's setting is ignored, and every object your body touches must be either a built-in or written with its schema spelled out (billing.vouchers). pg_temp goes last for the same reason — a caller can create temporary objects, and a temporary schema early in the path is the same attack wearing a different hat.

(2) And the same rule as views: revoke the direct access, or the function is a convenience rather than a control.

A SECURITY DEFINER function is a privilege boundary, so treat it like one. Keep it small, take only the parameters it needs, validate them at the top, and never let it accept a table name or a fragment of SQL as an argument.

6. The honest case against putting logic in the database

Stored procedures have swung between fashionable and forbidden about three times, and both extremes are wrong. Here is what the cost actually is.

Your logic now lives in two languages and two repositories. Half the checkout rule is in TypeScript and half is in PL/pgSQL. Anyone changing behaviour has to know both and remember which half owns which decision. This is the real cost, and it is not technical — it is that nobody can read the whole rule in one place.

Deployment is coupled to migrations. Changing a function means shipping a migration, which means the ordinary tools of a code change — a branch, a review, a rollback — go through a different pipeline than the application code that calls it. Some teams handle this well by keeping every function definition in a file and replaying them all on deploy. Many do not, and then the definition in production has drifted from the one in the repository, which nobody discovers until an incident.

The database is the hardest tier to scale. You add application servers by starting more of them. You do not add primary databases. So CPU spent inside the database is the most expensive CPU you own, and moving a heavy computation there to save a round trip can be a bad trade at scale even though it looks faster in a benchmark of one.

Observability is thinner. Your application traces, metrics and error tracking mostly stop at the database boundary. A failure inside a five-hundred-line procedure surfaces as one error message and a line number, and the tooling for stepping through it is nothing like what you have for application code.

Portability disappears. PL/pgSQL, T-SQL and PL/SQL are entirely different languages. A thousand lines of stored code is a genuine reason a company cannot change database engine, and that is sometimes a fine trade and should be a decision rather than an accident.

7. So when is it right?

Four cases, and the first two are strong.

When the check and the write must be inseparable. The opening example. Anything of the form "read a value, decide, then write based on it" is a race unless the read and the write are in one transaction with the right lock. A procedure makes that structurally impossible to get wrong from any client. The alternative — a transaction in the application — works too, and the procedure's advantage is that every writer gets the rule automatically, including the migration script and the operator typing at 2am. That is the same argument as constraints in Chapter 7.1 section 9.

When the round trips dominate. A loop doing 10,000 small statements from an application pays 10,000 network latencies. The same loop inside the database pays none. This is why bulk data manipulation and data migrations are one of the least controversial uses of stored code.

When you need a narrow privilege boundary. A SECURITY DEFINER function that does exactly one dangerous thing, exposed to a role that has nothing else. Nothing else in the database gives you that shape.

When the operation is genuinely about data, not about the business. Recomputing a denormalised total, maintaining a search vector, archiving. These are close to the data, rarely change, and belong nowhere else.

And the case against, stated as simply: if a rule is about the business rather than the data, if it changes often, or if it needs to call anything outside the database, it belongs in application code. "How often will this change, and who needs to read it?" decides it more reliably than any performance argument.

What the interviewer will push on

"Function or procedure — what is the difference?" A function returns a value and runs inside the caller's transaction; a procedure is invoked with CALL and may commit and roll back itself. Then give the reason that ability exists: committing in chunks inside a long batch, so one enormous transaction does not hold a snapshot and block vacuum. Reciting the syntax without naming the transaction difference is the weak answer.

"Why would you put logic in the database at all?" Two reasons that survive scrutiny: an atomic check-then-write that no client can get wrong, and killing round trips on bulk work. Then volunteer the cost — logic split across two languages and two deployment pipelines, and the database being the tier you cannot scale by adding machines.

"What does SECURITY DEFINER do, and what must you do with it?" Runs with the creator's privileges instead of the caller's. Then the part that separates a real answer: you must pin SET search_path = pg_catalog, pg_temp, or a caller who can create objects can put their own vouchers table earlier in the path and have your privileged function operate on it. Then revoke direct table access, or the function is decoration.

"What is function volatility for?" It tells the planner what it may do. IMMUTABLE lets a value be computed once and lets you build an index on it; STABLE lets it be used in an index condition; VOLATILE forces one call per row. The tell is knowing the promise is unchecked, so a false IMMUTABLE on an index expression silently returns wrong rows.

"Why might a function in a WHERE clause be slow?" Either it is VOLATILE and cannot be used in an index condition, so it runs once per row after the fetch, or it is a PL/pgSQL function that could not be inlined. The fix is usually labelling it STABLE and writing it in plain SQL so the planner can inline the body into the calling query.

One thing to volunteer: mention that every BEGIN … EXCEPTION block in PL/pgSQL opens a subtransaction, so an exception handler inside a loop over a hundred thousand rows creates a hundred thousand of them and the job crawls. INSERT … ON CONFLICT DO NOTHING handles the common case with none. It is a specific, measurable thing that only somebody who has profiled stored code knows.

Recall

• A function returns a value and runs inside the caller's transaction. A procedure is called with CALL and may COMMIT and ROLLBACK, which is the only reason PostgreSQL added them in version 11 — committing a long batch in chunks so it does not hold one snapshot open.
• A procedure cannot commit if it was called from inside an existing transaction block.
• The two things stored code genuinely buys: an atomic check-then-write no client can get wrong (SELECT … FOR UPDATE then update, in one transaction), and killing round trips on bulk work.
• Return shapes: scalar, RETURNS TABLE (…) or SETOF for many rows, and OUT parameters for several values.
• Volatility is a promise the planner believes and nobody checks. IMMUTABLE allows an index on the expression; STABLE allows use in an index condition; VOLATILE forces one call per row. A false IMMUTABLE silently returns wrong rows.
• A single-statement LANGUAGE sql function that is STABLE or IMMUTABLE and not SECURITY DEFINER can be inlined into the calling query and costs nothing. PL/pgSQL cannot be, and pays a call per row.
• PL/pgSQL caches plans per session and may switch to a generic plan after about five executions — the skewed-parameter trap again. EXECUTE format(…, %L) forces a fresh plan; never build SQL by concatenating a parameter.
• Every BEGIN … EXCEPTION block is a subtransaction. One inside a hot loop is a performance bug; ON CONFLICT DO NOTHING avoids it.
• SECURITY DEFINER runs as the creator. It requires SET search_path = pg_catalog, pg_temp, or a caller can plant their own object earlier in the path and have your privileged function use it. Then REVOKE the underlying table.
• The costs: logic split over two languages and two deploy pipelines, thinner observability, engine lock-in, and CPU spent on the one tier you cannot scale by adding machines.

Self-test: Which one can commit, and why does that matter for a long batch? · What exactly does FOR UPDATE prevent in reserve_stock? · Why does an index on a function require IMMUTABLE? · What makes a function inlinable? · Describe the search_path attack in two sentences · Why is an exception handler inside a loop expensive?

Next: 7.2.9 finishes the Part's SQL chapters with triggers — code the database runs for you on every write, what BEFORE can do that AFTER cannot, and the honest comparison of triggers against constraints, generated columns and plain application code.