User-Defined Functions

A user-defined function packages up a piece of logic — a calculation, a lookup, a transformation — under a name you can call from SQL, just like a built-in function such as COALESCE or LOWER. PostgreSQL functions are created with CREATE FUNCTION and most commonly written in PL/pgSQL.

Scalar functions

A scalar function returns a single value. Here's one that computes a discounted price:

SQL

CREATE FUNCTION discounted_price(price numeric, discount_pct numeric)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  RETURN round(price - (price * discount_pct / 100), 2);
END;
$$;

SELECT discounted_price(199.99, 20);

discounted_price
------------------
           159.99

Table-valued functions

A table-valued function returns a whole result set — a set of rows — rather than a single value, using either RETURNS TABLE or RETURNS SETOF. It can be queried from the FROM clause just like a real table:

SQL

CREATE FUNCTION products_below_price(max_price numeric)
RETURNS TABLE (id integer, name text, price numeric)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
  RETURN QUERY
    SELECT p.id, p.name, p.price
    FROM products p
    WHERE p.price <= max_price
    ORDER BY p.price;
END;
$$;

SELECT * FROM products_below_price(25.00);

id | name          | price
---+---------------+-------
 4 | USB Cable     | 8.99
 7 | Notebook      | 4.50
12 | Wireless Mouse| 19.99

Volatility: IMMUTABLE, STABLE, VOLATILE

Every PostgreSQL function has a volatility category, which tells the query planner how much it's allowed to assume about the function's behavior across calls. This is a genuinely important — and often overlooked — PostgreSQL-specific optimization detail, because the planner uses it to decide whether a function call can be cached, reordered, or skipped entirely for rows that don't matter.

Category	Meaning	Planner behavior
`IMMUTABLE`	Given the same arguments, always returns the same result, with no dependency on the database state	Can be evaluated once and the result reused/cached, even folded into an index
`STABLE`	Given the same arguments, returns the same result within a single statement/scan, but may depend on database state (e.g. reading a table)	Can be evaluated once per statement rather than once per row
`VOLATILE`	May return a different result on every call, even with identical arguments (e.g. `random()`, or anything with side effects)	Must be re-evaluated for every single row — no caching or reordering

Note

VOLATILE is the default if you don't specify a category, and it's always safe — it just gives up optimization opportunities. Declaring a function IMMUTABLE or STABLE when it genuinely qualifies can meaningfully speed up queries that call it repeatedly, but declaring it incorrectly (e.g. marking a function IMMUTABLE when it actually reads a table that changes) can lead to PostgreSQL caching a stale result and returning wrong answers. Only use IMMUTABLE/STABLE when the function genuinely behaves that way.

CREATE FUNCTION defines a scalar function (single value) or a table-valued function (RETURNS TABLE / RETURNS SETOF).
Table-valued functions can be queried directly in a FROM clause.
Volatility (IMMUTABLE / STABLE / VOLATILE) tells the planner how aggressively it can optimize calls to the function.
Getting volatility wrong in the "too optimistic" direction can produce incorrect query results, not just missed optimizations.