A function is a named piece of logic stored in the database: give it arguments, get a value back. Instead of repeating the same expression in every query — or shipping it to the application — you name it once and call it like any built-in.
The seed is a tiny products catalog with a net (pre-tax) price. We'll write functions that turn those prices into something useful.
SELECT * FROM products ORDER BY price;
The simplest function: LANGUAGE sql
A SQL function is just a single query with a name. Here's one that adds 21% tax to a net amount. The argument is referenced by name, and RETURNS declares the output type:
CREATE FUNCTION add_tax(amount numeric)
RETURNS numeric
LANGUAGE sql
AS $$
SELECT amount * 1.21;
$$;
Now call it like any function — in the SELECT list, over every row:
SELECT name, price, add_tax(price) AS gross
FROM products
ORDER BY price;
A LANGUAGE sql function is the right default for anything expressible as one query. The body is a plain SELECT, so the planner can often inline it — splice the expression straight into the calling query and optimize the whole thing together, as if you'd never written a function. Zero call overhead.
Naming arguments, and DEFAULT
Positional arguments get names in the signature; you can also give them defaults so callers may omit them. This version takes the tax rate as a second argument, defaulting to 0.21:
CREATE FUNCTION net_to_gross(amount numeric, rate numeric DEFAULT 0.21)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
SELECT amount * (1 + rate);
$$;
Call it with one argument (the default 21% applies) or two (override the rate):
SELECT net_to_gross(100.00) AS default_rate,
net_to_gross(100.00, 0.10) AS reduced_rate;
LANGUAGE plpgsql: variables and control flow
When one query isn't enough — you need variables, branching, or loops — reach for PL/pgSQL, Postgres's procedural language. The body lives in a // block. This function buckets a price into a size label using //: