PL/pgSQL Function Conventions — Volatility, RETURNS and the Boundary to Procedures

Published Jun 12, 2026
by

A PL/pgSQL function without a volatility marker is VOLATILE by default. That sounds harmless but has a real cost: the planner calls the function again for every row, never pre-computes it once, and excludes it from every functional index. The damage is invisible — until the same query suddenly takes seconds instead of milliseconds. Good PL/pgSQL function conventions start right here: not with naming, but with the question of what the function promises the planner.

This article is the function spoke of a small conventions series. The shared body rules (three-block structure, error messages, tabular alignment) are derived in the procedures article — here we focus on what sets functions apart from procedures.

What you’ll take away:

  • Volatility done right — IMMUTABLE, STABLE, VOLATILE and what the planner makes of them.
  • The boundary to procedures — what belongs in a function and what doesn’t.
  • The RETURNS contract — the return type as a deliberate contract, RETURNS/LANGUAGE set cleanly.
  • The lightweight variant — when the full procedure body is overkill.
  • The EXCEPTION → RETURN NULL fallback — how functions catch errors in a typed way.

Prerequisite: Postgres 12 or newer and basic PL/pgSQL knowledge. The examples run in a neutral schema app; replace it with your own.

Volatility: IMMUTABLE, STABLE, VOLATILE

This is the most important decision on a function — and the one most often forgotten. Every function gets one of three volatility classes. It is not a performance toggle you “optimize later”, but a promise to the planner that the planner relies on.

ClassPromiseThe planner may …
IMMUTABLESame arguments → always the same result. No DB access, no time, no settings.evaluate the call at plan time and inline it as a constant, cache the result, build functional indexes on it.
STABLEStable within one statement: same arguments → same result. May read the DB.use the call in an index scan and optimize more aggressively within a statement than for VOLATILE.
VOLATILENo guarantee. May return something different per call, may write.assume nothing — one call per row, no pre-computation, no index.

The default is VOLATILE. Omit the marker and you get the most expensive variant. For a pure computation, that’s wasted.

The volatility trio

The same mechanics across three mini-functions — each with the correct class:

  1: -- IMMUTABLE: pure computation, no outside world
  2: CREATE OR REPLACE FUNCTION app.fn_to_full_name
  3: (
  4:     IN    p_first   varchar
  5:    ,IN    p_last    varchar
  6: )
  7: RETURNS varchar
  8: LANGUAGE sql
  9: IMMUTABLE
 10: AS $function$
 11:    SELECT trim(concat_ws(' ', p_first, p_last));
 12: $function$;
 13:
 14: -- STABLE: reads the DB, but stable within one statement
 15: CREATE OR REPLACE FUNCTION app.fn_get_project_name
 16: (
 17:     IN    p_project_id   bigint
 18: )
 19: RETURNS varchar
 20: LANGUAGE sql
 21: STABLE
 22: AS $function$
 23:    SELECT name FROM app.project WHERE id = p_project_id;
 24: $function$;
 25:
 26: -- VOLATILE: non-deterministic (sequence side effect)
 27: CREATE OR REPLACE FUNCTION app.fn_next_counter()
 28: RETURNS bigint
 29: LANGUAGE sql
 30: VOLATILE
 31: AS $function$
 32:    SELECT nextval('app.counter_seq');
 33: $function$;

fn_to_full_name depends only on its arguments — IMMUTABLE. fn_get_project_name reads a table whose contents can change between statements — STABLE, not IMMUTABLE. fn_next_counter returns a new value on every call — VOLATILE. A sequence access is a perfectly legitimate VOLATILE case; but as soon as a function actually writes (INSERT/UPDATE/DELETE), that’s a sign a procedure fits better (more on that below).

The typical misclassifications

Two mistakes come up again and again:

  • Too optimistic: A function is marked IMMUTABLE even though it reads a table or uses time-/session-dependent values — clock_timestamp(), current_setting(), even now() (which is itself only STABLE and changes per transaction). Result: Postgres caches a result that isn’t constant. And because the planner trusts the IMMUTABLE promise, it even allows a functional index on it in the first place — which then becomes inconsistent as soon as the result changes. The function silently returns stale values. IMMUTABLE is a promise, not a wish; break it and you get wrong results, not an error.
  • Too pessimistic: A pure computation stays on the VOLATILE default. Result: no pre-computation at plan time, no index use, one call per row. Harmless for correctness, but noticeably slow on large queries.

The rule of thumb: as restrictive as is true. Depends only on its arguments → IMMUTABLE. Reads only → STABLE. Writes or is non-deterministic → VOLATILE (and check whether it shouldn’t be a procedure).

What belongs in a function — and what doesn’t

Since Postgres 11 there are real procedures (CREATE PROCEDURE, called via CALL). That makes a boundary drawable that used to be blurry:

  • Functions compute and return. Ideally called read-only or pure — often inside a query (SELECT app.fn_… FROM …). Their return value is the point.
  • Procedures orchestrate and write. Called via CALL as a standalone action, may control transactions (COMMIT/ROLLBACK), and are the right place for mutations.

From this follows the convention: writes belong in procedures, not functions, as a rule. Three reasons:

  1. Side effects inside a query are a trap. A function called in a SELECT over 10,000 rows runs once per row — and depending on the plan (filters before the function, join order, re-scans) more or fewer times than you’d naively expect. How often a VOLATILE function with a side effect actually runs is not reliably predictable.
  2. The call site hides the mutation. SELECT app.fn_archive(id) FROM … looks like a read. CALL app.sp_archive(...) honestly states that something is being changed.
  3. Transaction control exists only in procedures. A function always runs inside its caller’s transaction and cannot close it itself.

This is a deliberate default, not a Postgres ban. Functions may technically write (as VOLATILE), and before Postgres 11 — without CREATE PROCEDURE — that was the only way. The rule is a decision with a reason: keep the boundary and you get code whose read and write paths you recognize at the call site. The body structure for the write side is derived in the procedures article.

The function skeleton

A PL/pgSQL function with error handling follows the same file scaffold as a procedure, with function-specific points. The skeleton:

  1: DROP FUNCTION IF EXISTS app.fn_is_null_or_empty(varchar, bigint);
  2:
  3: -- --------------------------------------------------------------------------------
  4: -- Parameter
  5: -- --------------------------------------------------------------------------------
  6: --    p_value             varchar
  7: --       the text value to check
  8: --    p_min_length        bigint
  9: --       minimum length at which the value counts as "filled"
 10: -- --------------------------------------------------------------------------------
 11: CREATE OR REPLACE FUNCTION app.fn_is_null_or_empty
 12: (
 13:     IN    p_value             varchar
 14:    ,IN    p_min_length        bigint
 15: )
 16: RETURNS varchar
 17: LANGUAGE plpgsql
 18: IMMUTABLE
 19: AS $function$
 20: DECLARE
 21:    l_returnvalue             varchar;
 22: BEGIN
 23:
 24:    -- logic
 25:
 26:    RETURN l_returnvalue;
 27:
 28: EXCEPTION WHEN others THEN
 29:    RAISE NOTICE '##### %', SQLERRM;
 30:    RETURN NULL::varchar;
 31: END;
 32: $function$;
 33:
 34: ALTER FUNCTION app.fn_is_null_or_empty(varchar, bigint) OWNER TO app_owner;

The points that set a function apart from a procedure:

  • Naming fn_<verb>_<name>. The fn_ prefix makes the object type recognizable from the name (procedures: sp_, trigger functions: tf_). snake_case throughout, like the whole conventions corpus.
  • Dollar-quoting $function$. One tag per object type — functions $function$, procedures $procedure$. This makes the body instantly attributable when reading and avoids collisions when the body itself contains dollar-strings.
  • RETURNS and LANGUAGE each on their own line (lines 16–17). The return type stands isolated and is immediately visible when skimming — it is the function’s contract (more on that shortly).
  • Volatility right below (line 18). It belongs in the header, not at the end — whoever reads the signature sees at once what the function promises.
  • The -- Parameter doc block (lines 3–10) is part of this convention for any function with parameters, too. Per parameter, name + type, then the meaning — the bare signature is not enough.
  • EXCEPTION WHEN others THEN → RETURN NULL::<type> (lines 28–30). On error, it is logged as a notice and a typed NULL is returned instead of propagated. The ::varchar cast here is style and readability — it isn’t technically required: with a fixed RETURNS varchar, Postgres knows the target type and RETURN NULL; would do.

Careful — WHEN others is not a universal ending. Catching every error and returning NULL fits where NULL is a meaningful “no result”: in computing and checking functions whose contract is exactly that — invalid input yields NULL instead of a runtime error. That’s the same principle as TRY_CONVERT in SQL Server (see this blog’s data-quality articles). Where an error is instead a real failure the caller must see, blanket swallowing hurts: the cause disappears, and a “couldn’t” can no longer be told apart from a legitimate NULL. Then catch specific error classes or re-raise with RAISE;. A procedure, by contrast, typically sets a deterministic error status in its EXCEPTION block rather than swallowing the error.

The RETURNS contract

The return type is not a side detail but the function’s contract: every caller depends on it. That’s why it sits on its own line, and why the fallback in the EXCEPTION block makes the same type visible once more (NULL::varchar). A return type that changes later potentially breaks every calling expression — treat it like an API signature, not an implementation question.

Scalar returns (varchar, bigint, boolean, …) are the normal case. A function can also return a whole table (RETURNS TABLE (…) or SETOF) — but that has its own layout and performance rules and is a topic of its own (a separate article on table-returning functions is coming).

Validator and helper functions: the lightweight variant

Not every function needs the full scaffold. Pure validator and helper functions — that only compute or check and never raise — may be leaner:

  1: CREATE OR REPLACE FUNCTION app.fn_normalize_email
  2: (
  3:     IN    p_email   varchar
  4: )
  5: RETURNS varchar
  6: LANGUAGE plpgsql
  7: IMMUTABLE
  8: AS $function$
  9: BEGIN
 10:    RETURN lower(trim(p_email));
 11: END;
 12: $function$;

What’s missing here — and may be missing:

  • No Get name block. The full body structure from the procedures article starts with a diagnostics block that determines the component name (for error messages and logging). A function that never raises a RAISE EXCEPTION doesn’t need it.
  • Check parameter / Workload split optional. Splitting into collapsible BEGIN … END; sub-blocks only pays off once there are real input checks and a separate work phase. For a one-line computation it would be ballast.
  • Often no EXCEPTION block. If the computation can’t fail, there’s nothing to catch.

Such helpers are almost always IMMUTABLE (pure computation) and therefore the functions that benefit most from the correct volatility — they get reused across queries and functional indexes. The lightweight variant is not a compromise on quality, but the right size for the job: as much structure as needed, as little as possible.

How this convention came to be

These rules weren’t drawn up on a whiteboard, but distilled from real code: an existing collection of functions was analyzed with Claude Code, the recurring patterns extracted and condensed into a rules file that the assistant automatically enforces when generating new functions — the Generate → Refine → Derive loop. How this loop works and how to distill an enforceable convention from your own code is described in the methodology article. The bigger picture — AI-assisted SQL development with rules, skills and agents — is laid out in the pillar article.

Take it with you: the function conventions from this article are available as a ready-made starter rules file to download — drop it into your repo as .claude/rules/functions.md, and Claude Code picks it up as a project instruction from the next session on.

FAQ

What happens if I don’t specify the volatility?

Then the function is VOLATILE — the Postgres default. The planner treats it as unpredictable: one call per row, no pre-computation, no use in functional indexes. For a pure computation that’s wasted performance. Mark deliberately what is true: IMMUTABLE for pure computations, STABLE for read-only functions.

When do I use a function, when a procedure?

Rule of thumb: a function when a value is computed and returned, ideally read-only or pure. A procedure when something is written or orchestrated and the call is a standalone action (CALL). Functions are often called inside queries; procedures stand on their own.

Can a function write at all in Postgres?

Technically yes — a VOLATILE function can run INSERT/UPDATE/DELETE. Before Postgres 11 (without CREATE PROCEDURE) that was the only way. The convention here still advises against it: writes belong in procedures, because side effects inside a query are hard to predict and hide the call site.

Should a function catch every error with EXCEPTION WHEN others and return NULL?

Only when NULL is a meaningful “no result” — in computing and checking functions (the TRY_CONVERT principle: invalid input yields NULL instead of a runtime error). Otherwise no: a blanket WHEN others → RETURN NULL swallows the cause and makes a “couldn’t” indistinguishable from a real NULL. When in doubt, catch specific error classes or re-raise with RAISE;.

IMMUTABLE or STABLE — how do I tell them apart?

Ask yourself: does the result depend only on the arguments? Then IMMUTABLE. Does the function read anything from the database or environment (now(), current_setting(), a table)? Then STABLE at most, because that content can change between statements. Marking something IMMUTABLE that does read leads to wrong cached results — not to an error.

How do I carry this over to SQL Server?

SQL Server has scalar user-defined functions (UDFs) but no IMMUTABLE keyword. Determinism there is governed by WITH SCHEMABINDING and the determinism property of the expressions used; since SQL Server 2019 scalar UDFs can also be inlined into the statement. The underlying principle — mark what the optimizer may rely on, and keep writes out of computing functions — carries over; the syntax does not.