What is it? #
A function is saved logic that lives inside the database. You call it by name, it does work, and it returns a value.
PostgreSQL supports two shapes. A function returns something and is called from within a query: SELECT total_spent(42);. A procedure returns nothing, is called with CALL, and — importantly — can manage its own transactions.
Simple functions can be written in plain SQL. Anything needing variables, conditions, loops or error handling uses PL/pgSQL, PostgreSQL's procedural language.
The honest framing: functions are powerful and easy to overuse. Logic in the database is invisible to your application's tests, version control and debugger unless you deliberately manage it. Use them where they genuinely belong.
Think of it like this #
A saved recipe kept in the kitchen rather than in every cook's notebook.
The advantage is that everyone follows the same recipe and it is right next to the ingredients — no carrying things back and forth. A function that summarises a million rows does that work where the data already is, instead of shipping a million rows to your application.
The disadvantage is that the recipe is not in the cookbook everyone reviews. If it changes and nobody notices, dishes change silently. That is why database logic needs the same version control as application code.
Simple example #
Three realistic jobs: a small helper that computes a customer's lifetime spend, a function that safely transfers money between accounts with proper error handling, and a procedure that archives old orders in batches and commits as it goes.
Code #
SQL
-- ---------- A simple SQL function ----------
CREATE OR REPLACE FUNCTION total_spent(p_customer_id bigint)
RETURNS numeric
LANGUAGE sql
STABLE -- does not modify data; same inputs -> same result
AS <div class="katex-display-wrapper my-6 p-4 sm:p-6 rounded-2xl bg-paper-50 dark:bg-ink-900 border border-paper-200 dark:border-ink-800 shadow-sm overflow-x-auto text-center text-ink-900 dark:text-paper-100"><span class="katex-display"><span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML" display="block"><semantics><mrow><mi>S</mi><mi>E</mi><mi>L</mi><mi>E</mi><mi>C</mi><mi>T</mi><mi>C</mi><mi>O</mi><mi>A</mi><mi>L</mi><mi>E</mi><mi>S</mi><mi>C</mi><mi>E</mi><mo stretchy="false">(</mo><mi>s</mi><mi>u</mi><mi>m</mi><mo stretchy="false">(</mo><mi>t</mi><mi>o</mi><mi>t</mi><mi>a</mi><mi>l</mi><mo stretchy="false">)</mo><mo separator="true">,</mo><mn>0</mn><mo stretchy="false">)</mo><mi>F</mi><mi>R</mi><mi>O</mi><mi>M</mi><mi>o</mi><mi>r</mi><mi>d</mi><mi>e</mi><mi>r</mi><mi>s</mi><mi>W</mi><mi>H</mi><mi>E</mi><mi>R</mi><mi>E</mi><mi>c</mi><mi>u</mi><mi>s</mi><mi>t</mi><mi>o</mi><mi>m</mi><mi>e</mi><msub><mi>r</mi><mi>i</mi></msub><mi>d</mi><mo>=</mo><msub><mi>p</mi><mi>c</mi></msub><mi>u</mi><mi>s</mi><mi>t</mi><mi>o</mi><mi>m</mi><mi>e</mi><msub><mi>r</mi><mi>i</mi></msub><mi>d</mi><mi>A</mi><mi>N</mi><mi>D</mi><mi>s</mi><mi>t</mi><mi>a</mi><mi>t</mi><mi>u</mi><mi>s</mi><msup><mo>=</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>c</mi><mi>o</mi><mi>m</mi><mi>p</mi><mi>l</mi><mi>e</mi><mi>t</mi><mi>e</mi><msup><mi>d</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo separator="true">;</mo></mrow><annotation encoding="application/x-tex">SELECT COALESCE(sum(total), 0)
FROM orders
WHERE customer_id = p_customer_id
AND status = 'completed';</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal">L</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0715em;">C</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal" style="margin-right:0.0715em;">C</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal">A</span><span class="mord mathnormal">L</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.0715em;">C</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mopen">(</span><span class="mord mathnormal">s</span><span class="mord mathnormal">u</span><span class="mord mathnormal">m</span><span class="mopen">(</span><span class="mord mathnormal">t</span><span class="mord mathnormal">o</span><span class="mord mathnormal">t</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mclose">)</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord">0</span><span class="mclose">)</span><span class="mord mathnormal" style="margin-right:0.1389em;">F</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.109em;">M</span><span class="mord mathnormal" style="margin-right:0.0278em;">or</span><span class="mord mathnormal">d</span><span class="mord mathnormal" style="margin-right:0.0278em;">er</span><span class="mord mathnormal">s</span><span class="mord mathnormal" style="margin-right:0.1389em;">W</span><span class="mord mathnormal" style="margin-right:0.0813em;">H</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal">c</span><span class="mord mathnormal">u</span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">o</span><span class="mord mathnormal">m</span><span class="mord mathnormal">e</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3117em;"><span style="top:-2.55em;margin-left:-0.0278em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">i</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">d</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.9963em;vertical-align:-0.1944em;"></span><span class="mord"><span class="mord mathnormal">p</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">c</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">u</span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">o</span><span class="mord mathnormal">m</span><span class="mord mathnormal">e</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3117em;"><span style="top:-2.55em;margin-left:-0.0278em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">i</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">d</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.109em;">N</span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord mathnormal">u</span><span class="mord mathnormal">s</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel"><span class="mrel">=</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.8019em;"><span style="top:-3.113em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.9963em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">co</span><span class="mord mathnormal">m</span><span class="mord mathnormal" style="margin-right:0.0197em;">pl</span><span class="mord mathnormal">e</span><span class="mord mathnormal">t</span><span class="mord mathnormal">e</span><span class="mord"><span class="mord mathnormal">d</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.8019em;"><span style="top:-3.113em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mpunct">;</span></span></span></span></span></div>;
SELECT total_spent(42);
-- Because it is STABLE and written in SQL, PostgreSQL can often INLINE it
-- into the calling query and optimise the whole thing together.
SELECT c.name, total_spent(c.id) FROM customers c;
SQL
-- ---------- Volatility: tell PostgreSQL what your function does ----------
IMMUTABLE -- same inputs ALWAYS give the same result, forever.
-- e.g. lower(text). Can be used in index expressions.
STABLE -- same result within one statement, but may differ between
-- statements (it reads tables). Most read-only functions.
VOLATILE -- may return different results any time, or modifies data.
-- THE DEFAULT if you say nothing.
-- Declaring this correctly matters: a VOLATILE function cannot be optimised
-- away, so labelling a read-only function VOLATILE (or forgetting to label it)
-- can make queries far slower than necessary.
SQL
-- ---------- PL/pgSQL: variables, conditions, exceptions ----------
CREATE OR REPLACE FUNCTION transfer_money(
p_from bigint,
p_to bigint,
p_amount numeric
)
RETURNS boolean
LANGUAGE plpgsql
AS $$
DECLARE
v_balance numeric; -- variables are declared up front
BEGIN
IF p_amount <= 0 THEN
RAISE EXCEPTION 'Transfer amount must be positive, got %', p_amount;
-- ^ % is the placeholder
END IF;
-- Lock the source row so a concurrent transfer cannot race us
SELECT balance INTO v_balance
FROM accounts WHERE id = p_from
FOR UPDATE;
IF NOT FOUND THEN -- FOUND is set by the previous statement
RAISE EXCEPTION 'Account % does not exist', p_from;
END IF;
IF v_balance < p_amount THEN
RAISE EXCEPTION 'Insufficient funds: balance %, requested %',
v_balance, p_amount
USING ERRCODE = 'check_violation'; -- a catchable error code
END IF;
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
RETURN true;
EXCEPTION
WHEN foreign_key_violation THEN
RAISE NOTICE 'Target account % is invalid', p_to;
RETURN false;
END;
$$;
SELECT transfer_money(1, 2, 500);
TEXT
---------- A function is ALWAYS inside a transaction ----------
If a function raises an exception, EVERYTHING it did is rolled back,
including statements that already succeeded inside it.
That is usually exactly what you want: transfer_money above can never
subtract without adding.
It also means a FUNCTION CANNOT COMMIT. If you need to commit partway
through, you need a PROCEDURE (below).
SQL
-- ---------- Returning multiple rows ----------
CREATE OR REPLACE FUNCTION top_customers(p_limit int DEFAULT 10)
RETURNS TABLE (customer_id bigint, name text, spent numeric)
LANGUAGE sql
STABLE
AS <div class="katex-display-wrapper my-6 p-4 sm:p-6 rounded-2xl bg-paper-50 dark:bg-ink-900 border border-paper-200 dark:border-ink-800 shadow-sm overflow-x-auto text-center text-ink-900 dark:text-paper-100"><span class="katex-display"><span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML" display="block"><semantics><mrow><mi>S</mi><mi>E</mi><mi>L</mi><mi>E</mi><mi>C</mi><mi>T</mi><mi>c</mi><mi mathvariant="normal">.</mi><mi>i</mi><mi>d</mi><mo separator="true">,</mo><mi>c</mi><mi mathvariant="normal">.</mi><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mo separator="true">,</mo><mi>C</mi><mi>O</mi><mi>A</mi><mi>L</mi><mi>E</mi><mi>S</mi><mi>C</mi><mi>E</mi><mo stretchy="false">(</mo><mi>s</mi><mi>u</mi><mi>m</mi><mo stretchy="false">(</mo><mi>o</mi><mi mathvariant="normal">.</mi><mi>t</mi><mi>o</mi><mi>t</mi><mi>a</mi><mi>l</mi><mo stretchy="false">)</mo><mo separator="true">,</mo><mn>0</mn><mo stretchy="false">)</mo><mi>A</mi><mi>S</mi><mi>s</mi><mi>p</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>F</mi><mi>R</mi><mi>O</mi><mi>M</mi><mi>c</mi><mi>u</mi><mi>s</mi><mi>t</mi><mi>o</mi><mi>m</mi><mi>e</mi><mi>r</mi><mi>s</mi><mi>c</mi><mi>L</mi><mi>E</mi><mi>F</mi><mi>T</mi><mi>J</mi><mi>O</mi><mi>I</mi><mi>N</mi><mi>o</mi><mi>r</mi><mi>d</mi><mi>e</mi><mi>r</mi><mi>s</mi><mi>o</mi><mi>O</mi><mi>N</mi><mi>o</mi><mi mathvariant="normal">.</mi><mi>c</mi><mi>u</mi><mi>s</mi><mi>t</mi><mi>o</mi><mi>m</mi><mi>e</mi><msub><mi>r</mi><mi>i</mi></msub><mi>d</mi><mo>=</mo><mi>c</mi><mi mathvariant="normal">.</mi><mi>i</mi><mi>d</mi><mi>A</mi><mi>N</mi><mi>D</mi><mi>o</mi><mi mathvariant="normal">.</mi><mi>s</mi><mi>t</mi><mi>a</mi><mi>t</mi><mi>u</mi><mi>s</mi><msup><mo>=</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>c</mi><mi>o</mi><mi>m</mi><mi>p</mi><mi>l</mi><mi>e</mi><mi>t</mi><mi>e</mi><msup><mi>d</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>G</mi><mi>R</mi><mi>O</mi><mi>U</mi><mi>P</mi><mi>B</mi><mi>Y</mi><mi>c</mi><mi mathvariant="normal">.</mi><mi>i</mi><mi>d</mi><mo separator="true">,</mo><mi>c</mi><mi mathvariant="normal">.</mi><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mi>O</mi><mi>R</mi><mi>D</mi><mi>E</mi><mi>R</mi><mi>B</mi><mi>Y</mi><mi>s</mi><mi>p</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>D</mi><mi>E</mi><mi>S</mi><mi>C</mi><mi>L</mi><mi>I</mi><mi>M</mi><mi>I</mi><mi>T</mi><msub><mi>p</mi><mi>l</mi></msub><mi>i</mi><mi>m</mi><mi>i</mi><mi>t</mi><mo separator="true">;</mo></mrow><annotation encoding="application/x-tex">SELECT c.id, c.name, COALESCE(sum(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'completed'
GROUP BY c.id, c.name
ORDER BY spent DESC
LIMIT p_limit;</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal">L</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0715em;">C</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal">c</span><span class="mord">.</span><span class="mord mathnormal">i</span><span class="mord mathnormal">d</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal">c</span><span class="mord">.</span><span class="mord mathnormal">nam</span><span class="mord mathnormal">e</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal" style="margin-right:0.0715em;">C</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal">A</span><span class="mord mathnormal">L</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.0715em;">C</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mopen">(</span><span class="mord mathnormal">s</span><span class="mord mathnormal">u</span><span class="mord mathnormal">m</span><span class="mopen">(</span><span class="mord mathnormal">o</span><span class="mord">.</span><span class="mord mathnormal">t</span><span class="mord mathnormal">o</span><span class="mord mathnormal">t</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mclose">)</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord">0</span><span class="mclose">)</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal">s</span><span class="mord mathnormal">p</span><span class="mord mathnormal">e</span><span class="mord mathnormal">n</span><span class="mord mathnormal" style="margin-right:0.1389em;">tF</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.109em;">M</span><span class="mord mathnormal">c</span><span class="mord mathnormal">u</span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">o</span><span class="mord mathnormal">m</span><span class="mord mathnormal" style="margin-right:0.0278em;">er</span><span class="mord mathnormal">sc</span><span class="mord mathnormal">L</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.1389em;">F</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal" style="margin-right:0.0962em;">J</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.109em;">N</span><span class="mord mathnormal" style="margin-right:0.0278em;">or</span><span class="mord mathnormal">d</span><span class="mord mathnormal" style="margin-right:0.0278em;">er</span><span class="mord mathnormal">so</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.109em;">N</span><span class="mord mathnormal">o</span><span class="mord">.</span><span class="mord mathnormal">c</span><span class="mord mathnormal">u</span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">o</span><span class="mord mathnormal">m</span><span class="mord mathnormal">e</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3117em;"><span style="top:-2.55em;margin-left:-0.0278em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">i</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">d</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.8019em;"></span><span class="mord mathnormal">c</span><span class="mord">.</span><span class="mord mathnormal">i</span><span class="mord mathnormal">d</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.109em;">N</span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal">o</span><span class="mord">.</span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord mathnormal">u</span><span class="mord mathnormal">s</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel"><span class="mrel">=</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.8019em;"><span style="top:-3.113em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.9963em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">co</span><span class="mord mathnormal">m</span><span class="mord mathnormal" style="margin-right:0.0197em;">pl</span><span class="mord mathnormal">e</span><span class="mord mathnormal">t</span><span class="mord mathnormal">e</span><span class="mord"><span class="mord mathnormal">d</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.8019em;"><span style="top:-3.113em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mord mathnormal" style="margin-right:0.0077em;">GR</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord mathnormal" style="margin-right:0.1389em;">P</span><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord mathnormal" style="margin-right:0.2222em;">Y</span><span class="mord mathnormal">c</span><span class="mord">.</span><span class="mord mathnormal">i</span><span class="mord mathnormal">d</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal">c</span><span class="mord">.</span><span class="mord mathnormal">nam</span><span class="mord mathnormal">e</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord mathnormal" style="margin-right:0.2222em;">Y</span><span class="mord mathnormal">s</span><span class="mord mathnormal">p</span><span class="mord mathnormal">e</span><span class="mord mathnormal">n</span><span class="mord mathnormal">t</span><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.0715em;">C</span><span class="mord mathnormal">L</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.109em;">M</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord"><span class="mord mathnormal">p</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3361em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0197em;">l</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">imi</span><span class="mord mathnormal">t</span><span class="mpunct">;</span></span></span></span></span></div>;
SELECT * FROM top_customers(5); -- used like a table
SQL
-- ---------- Loops in PL/pgSQL ----------
CREATE OR REPLACE FUNCTION recalculate_order_totals()
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
r record; -- holds one row at a time
v_count integer := 0;
BEGIN
FOR r IN SELECT id FROM orders WHERE status = 'pending'
LOOP
UPDATE orders o
SET total = (SELECT COALESCE(sum(quantity * unit_price), 0)
FROM order_items WHERE order_id = r.id)
WHERE o.id = r.id;
v_count := v_count + 1;
END LOOP;
RETURN v_count;
END;
$$;
-- NOTE: this is a teaching example. A single set-based UPDATE would be
-- far faster than looping. Loop only when the logic genuinely requires
-- row-by-row processing.
SQL
-- ---------- WHILE and simple LOOP ----------
WHILE v_remaining > 0 LOOP
-- ... do work ...
v_remaining := v_remaining - 1;
END LOOP;
LOOP
-- ... do work ...
EXIT WHEN v_done; -- explicit exit condition
END LOOP;
SQL
-- ---------- Procedures: can COMMIT mid-way ----------
CREATE OR REPLACE PROCEDURE archive_old_orders(p_before date)
LANGUAGE plpgsql
AS $$
DECLARE
v_moved integer;
BEGIN
LOOP
WITH moved AS (
DELETE FROM orders
WHERE placed_at < p_before
AND id IN (SELECT id FROM orders
WHERE placed_at < p_before LIMIT 1000)
RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;
GET DIAGNOSTICS v_moved = ROW_COUNT;
EXIT WHEN v_moved = 0;
COMMIT; -- <- ONLY a procedure can do this
RAISE NOTICE 'Archived % rows', v_moved;
END LOOP;
END;
$$;
CALL archive_old_orders('2020-01-01'); -- procedures use CALL, not SELECT
-- Committing in batches keeps each transaction short, so locks are released
-- regularly and VACUUM is not blocked for the whole operation.
SQL
-- ---------- Security: SECURITY DEFINER needs care ----------
CREATE OR REPLACE FUNCTION masked_customer_emails()
RETURNS TABLE (id bigint, masked text)
LANGUAGE sql
SECURITY DEFINER -- runs with the FUNCTION OWNER's privileges,
-- not the caller's
SET search_path = public, pg_temp -- !! REQUIRED with SECURITY DEFINER
AS <div class="katex-display-wrapper my-6 p-4 sm:p-6 rounded-2xl bg-paper-50 dark:bg-ink-900 border border-paper-200 dark:border-ink-800 shadow-sm overflow-x-auto text-center text-ink-900 dark:text-paper-100"><span class="katex-display"><span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML" display="block"><semantics><mrow><mi>S</mi><mi>E</mi><mi>L</mi><mi>E</mi><mi>C</mi><mi>T</mi><mi>i</mi><mi>d</mi><mo separator="true">,</mo><mi>r</mi><mi>e</mi><mi>g</mi><mi>e</mi><mi>x</mi><msub><mi>p</mi><mi>r</mi></msub><mi>e</mi><mi>p</mi><mi>l</mi><mi>a</mi><mi>c</mi><mi>e</mi><mo stretchy="false">(</mo><mi>e</mi><mi>m</mi><mi>a</mi><mi>i</mi><mi>l</mi><msup><mo separator="true">,</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo stretchy="false">(</mo><mi mathvariant="normal">.</mi><mo stretchy="false">)</mo><mi mathvariant="normal">.</mi><mo>∗</mo><mo stretchy="false">(</mo><mi mathvariant="normal">@</mi><mi mathvariant="normal">.</mi><mo>∗</mo><msup><mo stretchy="false">)</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><msup><mo separator="true">,</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mstyle mathcolor="#cc0000"><mtext>\1</mtext></mstyle><mo>∗</mo><mo>∗</mo><mo>∗</mo><msup><mstyle mathcolor="#cc0000"><mtext>\2</mtext></mstyle><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo stretchy="false">)</mo><mi>F</mi><mi>R</mi><mi>O</mi><mi>M</mi><mi>c</mi><mi>u</mi><mi>s</mi><mi>t</mi><mi>o</mi><mi>m</mi><mi>e</mi><mi>r</mi><mi>s</mi><mo separator="true">;</mo></mrow><annotation encoding="application/x-tex">SELECT id, regexp_replace(email, '(.).*(@.*)', '\1***\2') FROM customers;</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1.0519em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal">L</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0715em;">C</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal">i</span><span class="mord mathnormal">d</span><span class="mpunct">,</span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="mord mathnormal">e</span><span class="mord mathnormal" style="margin-right:0.0359em;">g</span><span class="mord mathnormal">e</span><span class="mord mathnormal">x</span><span class="mord"><span class="mord mathnormal">p</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0278em;">r</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">e</span><span class="mord mathnormal" style="margin-right:0.0197em;">pl</span><span class="mord mathnormal">a</span><span class="mord mathnormal">ce</span><span class="mopen">(</span><span class="mord mathnormal">e</span><span class="mord mathnormal">mai</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mpunct"><span class="mpunct">,</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.8019em;"><span style="top:-3.113em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mopen">(</span><span class="mord">.</span><span class="mclose">)</span><span class="mord">.</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">∗</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:1.0519em;vertical-align:-0.25em;"></span><span class="mopen">(</span><span class="mord">@.</span><span class="mord">∗</span><span class="mclose"><span class="mclose">)</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.8019em;"><span style="top:-3.113em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mpunct"><span class="mpunct">,</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.8019em;"><span style="top:-3.113em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mspace" style="margin-right:0.1667em;"></span><span class="mord text" style="color:#cc0000;"><span class="mord" style="color:#cc0000;">\1</span></span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">∗</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.4653em;"></span><span class="mord">∗</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">∗</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:1.1418em;vertical-align:-0.25em;"></span><span class="mord"><span class="mord text" style="color:#cc0000;"><span class="mord" style="color:#cc0000;">\2</span></span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.8918em;"><span style="top:-3.2029em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mclose">)</span><span class="mord mathnormal" style="margin-right:0.1389em;">F</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.109em;">M</span><span class="mord mathnormal">c</span><span class="mord mathnormal">u</span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">o</span><span class="mord mathnormal">m</span><span class="mord mathnormal" style="margin-right:0.0278em;">er</span><span class="mord mathnormal">s</span><span class="mpunct">;</span></span></span></span></span></div>;
-- Without "SET search_path", a caller could create their own table or function
-- earlier in their search path and have it run with the owner's privileges.
-- ALWAYS set search_path on SECURITY DEFINER functions.
SQL
-- ---------- Inspecting and dropping ----------
\df -- list functions (psql)
\sf total_spent -- show a function's source
DROP FUNCTION IF EXISTS total_spent(bigint);
-- The ARGUMENT TYPES are part of the identity: functions can be overloaded,
-- so you must specify which one you mean.How it works #
A plain SQL function is a saved query with parameters. When it is marked STABLE or IMMUTABLE and is simple enough, PostgreSQL can inline it into the calling query — substituting the body directly and then optimising everything together. That is why a small SQL function used inside a larger query often costs nothing at all.
Volatility is the label that makes this possible, and getting it wrong has real cost. IMMUTABLE means the same inputs always produce the same output, which also makes the function usable in an index expression. STABLE means consistent within a single statement, which covers most read-only functions. VOLATILE is the default and blocks these optimisations, so an unlabelled read-only function is quietly slower than it should be.
PL/pgSQL adds the procedural pieces: DECLARE for variables, IF, loops, and EXCEPTION blocks. SELECT ... INTO variable assigns a query result to a variable, and the special FOUND variable tells you whether the previous statement matched anything.
The most important structural fact: a function always runs inside a transaction. If it raises an exception, everything it did is rolled back — including statements that had already succeeded within it. That is exactly what makes transfer_money safe. The corollary is that a function cannot COMMIT, because it does not own the transaction it runs in.
Procedures exist for that gap. Called with CALL rather than SELECT, they can commit and start new transactions. This is what makes batched maintenance possible: archive a thousand rows, commit, repeat. Each batch is a short transaction, so locks are released regularly and VACUUM is not blocked for hours.
An EXCEPTION block is internally a savepoint, which means it has a real cost. A loop with an exception block on every iteration is noticeably slower than one without, so catch only what you actually intend to handle.
SECURITY DEFINER runs the function with the owner's privileges instead of the caller's. It is genuinely useful for letting a restricted role perform a narrow privileged action — but without SET search_path, a caller can place their own object earlier in the search path and have it executed with the owner's rights. Always set search_path on SECURITY DEFINER functions.
Real-world use #
The best uses of database functions share a property: they keep work close to the data. Aggregating a million rows into one number inside the database, rather than transferring a million rows to the application, is an enormous saving. So is a data-integrity rule that must hold no matter which client writes.
The genuine downsides deserve equal weight. Database logic is invisible to your application's test suite unless you build for it. It does not appear in code review unless migrations are reviewed properly. Debugging is harder — no breakpoints, no stack traces of the kind application developers expect. And deploying a change means a migration, not a code release.
The practical resolution most teams reach: keep functions in version-controlled migration files, never edit them directly in production, and reserve them for cases that clearly justify the trade-off. Business logic that changes often usually belongs in the application; invariants that must never be violated often belong in the database.
Watch for loops that should be set operations. FOR r IN SELECT ... LOOP UPDATE ... END LOOP is the most common performance mistake in PL/pgSQL — a single UPDATE ... FROM is frequently hundreds of times faster. Loop only when the logic genuinely requires row-by-row processing, such as calling an external routine per row or committing in batches.
Batched procedures are the right tool for large data maintenance: archiving, backfilling a new column, migrating data between tables. One enormous transaction doing the same work holds locks for its entire duration and generates a huge amount of WAL at once. Batching with COMMIT avoids both problems.
Common mistakes #
- Leaving a read-only function VOLATILE (the default), preventing inlining and slowing queries.
- Writing a row-by-row loop where a single set-based UPDATE would be far faster.
- Expecting a function to COMMIT partway through — only a procedure can do that.
- Creating SECURITY DEFINER functions without SET search_path, which is a real privilege-escalation risk.
- Editing functions directly in production instead of keeping them in version-controlled migrations.
Practice #
Write a STABLE SQL function returning a customer's lifetime spend, and confirm it can be used inside a larger query. Then write a PL/pgSQL function that transfers money between two accounts with FOR UPDATE locking, raises a clear exception for insufficient funds, and verify that a failed transfer leaves both balances unchanged. Finally, write a procedure that archives old orders 500 at a time and commits between batches, and watch the RAISE NOTICE output as it runs.