# PL/pgSQL Function Conventions — Starter Rules for Claude Code > Drop this file into your repository as `.claude/rules/functions.md`. Claude Code loads it > as a project instruction every session and generates functions in this house style. > > Distilled from a production project; project-specific internals removed. The `app` schema > is a placeholder — replace it with your own. Treat every rule as **Rule → Reason → Example**: > the *reason* lets the agent generalize to cases this file doesn't spell out. > > This file covers **functions**. Procedures, tables and triggers have their own files. ## Volatility: the most important marker Every function gets one of three volatility classes. It is not a performance toggle to add "later" — it is a **promise to the planner**, and the planner relies on it. | Class | Promise | The planner may … | |-------------|------------------------------------------------------------------------------|------------------------------------------------------------------------------------| | `IMMUTABLE` | Same 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 it, build **functional indexes** on it. | | `STABLE` | Stable within a **single** statement: same arguments → same result. May read. | use the call in an index scan and optimize more within a statement than for `VOLATILE`. | | `VOLATILE` | No 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. - **Rule: as restrictive as is true.** Depends only on its arguments → `IMMUTABLE`. Reads only → `STABLE`. Writes or is non-deterministic → `VOLATILE` (and check whether it should be a procedure). - **`IMMUTABLE` is a promise, not a wish.** Marking something `IMMUTABLE` that actually reads a table or uses `now()` / `current_setting()` makes Postgres cache a result that isn't constant — you get wrong cached values and inconsistent functional indexes, not an error. ```sql -- IMMUTABLE: pure computation CREATE OR REPLACE FUNCTION app.fn_to_full_name(p_first varchar, p_last varchar) RETURNS varchar LANGUAGE sql IMMUTABLE AS $function$ SELECT trim(concat_ws(' ', p_first, p_last)); $function$; ``` ## Functions vs. procedures - **Functions compute and return.** Ideally read-only or pure; often called *inside a query*. - **Procedures orchestrate and write.** Called via `CALL` as a standalone action; may control transactions (`COMMIT` / `ROLLBACK`). - **Rule: writes belong in procedures, not functions.** A function called in a `SELECT` over 10 000 rows runs once per row — and, depending on the plan, more or fewer times than you'd expect; how often a `VOLATILE` function with a side effect actually runs is not reliably predictable. `CALL app.sp_archive(...)` also states honestly that something is being changed, whereas `SELECT app.fn_archive(id)` looks like a read. (A sequence access like `nextval()` is a legitimate `VOLATILE` function — the rule targets `INSERT`/`UPDATE`/`DELETE` writes.) - This is a deliberate default, **not** a Postgres ban — functions *may* write (as `VOLATILE`), and before Postgres 11 (no `CREATE PROCEDURE`) that was the only way. ## The function skeleton ```sql DROP FUNCTION IF EXISTS app.fn_is_null_or_empty(varchar, bigint); -- -------------------------------------------------------------------------------- -- Parameter -- -------------------------------------------------------------------------------- -- p_value varchar -- the text value to check -- p_min_length bigint -- minimum length at which the value counts as "filled" -- -------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION app.fn_is_null_or_empty ( IN p_value varchar ,IN p_min_length bigint ) RETURNS varchar LANGUAGE plpgsql IMMUTABLE AS $function$ DECLARE l_returnvalue varchar; BEGIN -- logic RETURN l_returnvalue; EXCEPTION WHEN others THEN RAISE NOTICE '##### %', SQLERRM; RETURN NULL::varchar; END; $function$; ALTER FUNCTION app.fn_is_null_or_empty(varchar, bigint) OWNER TO app_owner; ``` What sets a function apart from a procedure: - **Naming `fn__`** (procedures: `sp_`, trigger functions: `tf_`). Always `snake_case`. - **Dollar-quoting `$function$`** — one tag per object type, so the body is instantly recognizable and dollar-strings inside it don't collide. - **`RETURNS` and `LANGUAGE` each on their own line**, with the **volatility marker right below** — whoever reads the signature sees what the function promises. - **The `-- Parameter` doc block** is mandatory for any function with parameters: name + type, then the meaning. The bare signature is not enough. - **`EXCEPTION WHEN others THEN → RETURN NULL::`** — on error, log a notice and return a **typed NULL** instead of propagating. The `::` cast is style/readability, not technically required (with a fixed `RETURNS`, `RETURN NULL;` suffices). **Use this only where `NULL` is a meaningful "no result"** — validator/converter functions (the `TRY_CONVERT` principle). Where the caller must see real failures, catch specific errors or re-raise with `RAISE;` instead of swallowing them. (A procedure, by contrast, sets a deterministic error status rather than swallowing the error.) ### The RETURNS contract The return type is the function's **contract** — every caller depends on it. That is why it sits on its own line and why the `EXCEPTION` fallback is cast to exactly that type (`NULL::varchar`). Treat a return-type change like an API-signature change, not an implementation detail. Scalar returns (`varchar`, `bigint`, `boolean`, …) are the normal case. Set returns (`RETURNS TABLE (…)` / `SETOF`) have their own layout and performance rules — out of scope here. ## Validator and helper functions: the lightweight variant Pure validator/helper functions — that only compute or check and never raise — may be leaner: ```sql CREATE OR REPLACE FUNCTION app.fn_normalize_email(p_email varchar) RETURNS varchar LANGUAGE plpgsql IMMUTABLE AS $function$ BEGIN RETURN lower(trim(p_email)); END; $function$; ``` - **No `Get name` block** — a function that never raises an exception doesn't need the component-name diagnostics the full body uses. - **`Check parameter` / `Workload` split optional** — only worth it when there are real input checks *and* a separate work phase. - **Often no `EXCEPTION` block** — if the computation can't fail, there's nothing to catch. Such helpers are almost always `IMMUTABLE` and benefit most from the correct volatility, because they get reused across queries and functional indexes. Lightweight is not less quality — it is the right size for the job: as much structure as needed, as little as possible. ## Shared rules (parameters, alignment, errors) These apply to functions exactly as to procedures — see the procedures rules file: - **Parameters prefixed `p_`**, locals `l_`; the mode keyword (`IN` / `OUT` / `INOUT`) carries the direction, don't encode it in the name. - **Identifier parameter first** in the signature, then attribute parameters. - **Never hard-code the schema name** — use a variable or qualify consistently. - **Tabular alignment** of parameters, variable declarations and JOINs; positional table aliases `T01`, `T02`. - **Error messages via `format($$…$$, …)`** with indexed placeholders, message and code in variables first, original ERRCODE preserved. --- *Based on the article „PL/pgSQL-Funktions-Konventionen — Volatilität, RETURNS und die Grenze zur Prozedur" — sql.marcus-belz.de*