SQL / SECURITY, ROUTINES, AND DIALECTS
User-defined functions and where they belong
Write scalar and set-returning SQL functions, declare their volatility honestly, and decide which rules belong in the database rather than the app.
What you will learn
- Write a scalar SQL function and call it from SELECT, WHERE, CHECK, or an index
- Pick IMMUTABLE, STABLE, or VOLATILE from what the function body actually reads
- Spot a scalar UDF that hides a per-row query or shuts an index out of a filter
- Return a set and use it in FROM instead of calling a scalar function per row
Understanding User-defined functions and where they belong
A user-defined function returns a value and is called from inside an expression. That is the whole boundary: because CREATE FUNCTION adds new vocabulary to the expression language, a function can appear in a SELECT list, a WHERE clause, a CHECK constraint, an index definition, a generated column, and, when it returns a set, in FROM. Anything invoked as a statement of its own cannot appear in those places, which is why the choice between the two forms is decided by where you need the logic, not by how much code it contains.
Once your code is part of an expression, the planner needs to know how it behaves, so every engine makes you declare something: PostgreSQL has IMMUTABLE, STABLE and VOLATILE; MySQL wants DETERMINISTIC plus a data-access flag; SQL Server leans on schema binding. The declaration is a promise the engine believes rather than verifies. IMMUTABLE means the result depends only on the arguments, so the value can be computed once, folded into a constant, or stored as an index key; VOLATILE means it must be re-evaluated for every row. The costs follow from the same fact: a scalar function containing a SELECT becomes one query per row, and writing f(col) in a filter hides col from its index, because a B-tree on col stores raw values and knows nothing about f.
So a rule belongs in a function in the database when it is a property of the data itself: the normalisation a unique index must agree on, an invariant every writer has to satisfy, a derivation several queries repeat identically. It belongs in the application when it touches outside systems, formats output for one screen, or changes on a faster release cycle than the schema. Remember that a function body is schema you must migrate, and its dependents do not recompute themselves: change the body and existing expression-index entries, generated column values, and already-validated CHECK rows keep the old answers. Keep the functions small, pure and explicitly typed, and prefer a join or a set-returning function over a scalar one applied row by row.
CREATE TABLE orders (id int, qty int, unit_ct int);
INSERT INTO orders VALUES (1, 3, 499), (2, 1, 1250), (3, 10, 99);
-- Reads nothing but its arguments, so the IMMUTABLE promise is true.
CREATE FUNCTION line_total_ct(qty int, unit_ct int, tax_bp int)
RETURNS int
LANGUAGE sql
IMMUTABLE
AS $$
SELECT (qty * unit_ct) + (qty * unit_ct * tax_bp) / 10000
$$;
SELECT id, line_total_ct(qty, unit_ct, 875) AS total_ct
FROM orders
WHERE line_total_ct(qty, unit_ct, 875) > 1300
ORDER BY id;A user-defined function adds a new expression to the query language, so its purity and per-row cost become the planner's problem, which is why only rules the data itself owns belong there.
Worked examples
A set-returning function is a view that takes an argument
Returning a table instead of a scalar keeps the work in one set-based query rather than one call per row.
CREATE TABLE readings (sensor text, celsius numeric(4,1));
INSERT INTO readings VALUES ('a', 21.5), ('a', 23.0), ('b', 19.0);
CREATE FUNCTION hot_readings(min_c numeric)
RETURNS TABLE (sensor text, celsius numeric)
LANGUAGE sql
STABLE
AS $$
SELECT r.sensor, r.celsius
FROM readings r
WHERE r.celsius >= min_c
$$;
SELECT * FROM hot_readings(21.0) ORDER BY sensor, celsius;Example explained
Line 1RETURNS TABLE names the output columns, which is what lets the call sit in FROM like a parameterised view.
Line 2The body qualifies columns as r.sensor and r.celsius because those output names are also visible inside the body and would otherwise be ambiguous.
Line 3STABLE, not IMMUTABLE, is honest here: the answer depends on the current contents of readings, not only on min_c.
Line 4One call yields the whole set, so the planner can join or filter it further instead of invoking a scalar function once per candidate row.
IMMUTABLE earns the right to be indexed
A normalising function becomes enforceable data-level logic once a unique index is built on its result.
CREATE FUNCTION norm_sku(raw text) RETURNS text
LANGUAGE sql IMMUTABLE STRICT
AS $$ SELECT upper(replace(trim(raw), '-', '')) $$;
CREATE TABLE parts (sku text);
CREATE UNIQUE INDEX parts_sku_norm ON parts (norm_sku(sku));
INSERT INTO parts VALUES ('ab-123');
INSERT INTO parts VALUES (' AB123 ');Example explained
Line 1IMMUTABLE is not decoration: PostgreSQL rejects an index expression whose function is not marked immutable, because a stored key that can change meaning is a corrupt key.
Line 2The index keys are the normalised strings, so 'ab-123' and ' AB123 ' collide even though the two sku values are different text.
Line 3STRICT makes norm_sku(NULL) return NULL without running the body, and a unique index does not treat NULLs as duplicates of each other.
Line 4The rule now lives next to the data, so a client you did not write hits the same constraint without importing any of your code.
Important notes
This corner of SQL splits hard by engine: PostgreSQL can inline a simple LANGUAGE sql function into the calling query, MySQL needs DETERMINISTIC and data-access flags and does not inline, SQL Server only gained scalar UDF inlining in 2019, and SQLite has no CREATE FUNCTION at all because functions are registered from the host language.
Some engines let a function modify data. Keep UDFs read-only anyway, because the planner is free to call one more often, less often, or in a different order than your query text suggests.
Common mistakes
Marking a function IMMUTABLE while it selects from a table. The engine takes the promise at face value, folds the value or stores it in an index, and later queries return answers that no longer match the underlying rows.
Hiding a lookup query inside a scalar function and calling it in WHERE. The filter runs that query once per row, so a million-row scan fires a million small queries while the plan shows nothing but one harmless function call.
Expecting the index on sku to serve WHERE norm_sku(sku) = 'AB123'. The index stores raw sku values, so you get a full scan until you either index norm_sku(sku) or store the normalised value at write time.
Try it yourself
Change, predict, then run
In a PostgreSQL editor, create an IMMUTABLE function returning lower(trim($1)) and a STABLE function that counts matching rows in a small table, then try CREATE INDEX on each expression and read the error the STABLE one produces.
Open the SQL workspaceCheck your understanding
A query filtering WHERE norm_sku(sku) = 'AB123' on a million-row table reads every row, even though sku has a B-tree index. Why?
- Scalar functions are not allowed in WHERE, so the planner discards the predicate and scans.
- The function is marked IMMUTABLE, and immutable functions force a sequential scan.
- The index keys are raw sku values, not norm_sku(sku), so they cannot answer that comparison.
- The table statistics are stale, and ANALYZE will make the existing index usable.
Show answer
An index can only satisfy a comparison against the keys it actually stores; matching 'AB123' against normalised values requires an index whose keys are norm_sku(sku). IMMUTABLE is the opposite of a problem here, since it is the precondition for building that expression index, and no amount of ANALYZE can invent a key the index does not contain.