Skip to content
Tutorials Center
My learning

Topics

Lesson 49 of 90 ยท Functions, PL/pgSQL and Triggers

SQL Functions and Procedures

Write PostgreSQL functions in plain SQL: RETURNS TABLE and SETOF, IMMUTABLE vs STABLE vs VOLATILE, inlining, and CREATE PROCEDURE with CALL.

A function gives a name to a piece of logic that lives inside the database. Instead of repeating the same expression in twenty queries, you write it once, and every query, view, index and report can call it. PostgreSQL lets you write functions in several languages. The simplest one is plain SQL: the body is a single query or a short list of statements, with no variables or loops.

SQL functions are worth learning first because they are small, fast and easy to reason about. The planner can often inline them, which means it pastes the body into the calling query and optimizes the whole thing as one statement. They also teach the ideas every other PostgreSQL function shares: argument types, return types, set-returning functions and volatility.

What runs live here. This site runs queries in an in-browser SQLite engine. SQLite has no CREATE FUNCTION, so every PostgreSQL function and procedure in this lesson is a read-only code block. Where it helps, a live example shows the nearest SQLite equivalent: a view, a CTE or an inline expression. Each block is labelled so you always know which is which.

Your first SQL function

Say your shop shows prices with tax in many places. The tax rule is one line of arithmetic, but you do not want it copied everywhere. Here is a PostgreSQL function that wraps it (read-only):

SQL
CREATE FUNCTION price_with_tax(net numeric, rate numeric DEFAULT 0.20)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT round(net * (1 + rate), 2);
$$;

SELECT price_with_tax(10);          -- 12.00
SELECT price_with_tax(10, 0.07);    -- 10.70
SELECT name, price_with_tax(price) FROM products;

The pieces, from top to bottom:

  • CREATE FUNCTION name(arguments) declares the name and the typed parameters. A parameter can have a DEFAULT, which makes it optional at the call site.
  • RETURNS numeric is the result type. A function returns exactly one value, unless you declare it as returning a set (more on that below).
  • LANGUAGE sql selects the language. The body is plain SQL.
  • IMMUTABLE promises the planner something about the function. Hold that thought, volatility has its own section.
  • AS $$ ... $$ holds the body as a string. The dollar signs are called dollar quoting: they let you write single quotes inside the body without escaping them.

In a SQL function the last statement provides the result. For a scalar function that is normally one SELECT that returns one column. If the query returns several rows, the function returns only the first one, which is almost never what you want, so write queries that return exactly one row.

You can refer to parameters by name (net, rate) or by position ($1, $2). Names are easier to read. If a parameter name matches a column name in the query, the column wins, so prefix parameters with the function name (price_with_tax.net) or choose distinctive names such as p_net.

Since PostgreSQL 14 you can also write the body in the standard style, without a string: RETURN round(net * (1 + rate), 2); for a single expression, or BEGIN ATOMIC ... END for several statements. Those bodies are parsed when the function is created, so PostgreSQL records their dependencies and a dropped table cannot silently break them.

The live equivalent: a calculation you name once

SQLite cannot store a function, but a view can store the formula as a column, and every query then reuses it. This is what you can run to get the same effect.

SQL A view as a named formula

Press Run (or Ctrl + Enter) to see the result here.

The view does for a calculation on a row what a scalar SQL function does for an expression: you change the tax rate in one place and every reader gets the new value. Views cannot take parameters, though. That is where functions earn their keep.

Functions that return a table

A function can return many rows. PostgreSQL has two spellings. RETURNS SETOF some_table returns rows shaped like an existing table or type. RETURNS TABLE (col type, ...) declares the columns inline:

SQL
CREATE FUNCTION top_customers(min_orders int DEFAULT 3)
RETURNS TABLE (customer_id int, orders bigint, revenue numeric)
LANGUAGE sql
STABLE
AS $$
  SELECT o.customer_id, count(*), sum(o.total)
  FROM orders AS o
  GROUP BY o.customer_id
  HAVING count(*) >= min_orders
  ORDER BY sum(o.total) DESC;
$$;

SELECT * FROM top_customers();       -- uses the default of 3
SELECT * FROM top_customers(10);     -- only very loyal customers
SELECT c.name, t.revenue
FROM top_customers(5) AS t
JOIN customers AS c ON c.id = t.customer_id;

You call a set-returning function in the FROM clause, and it behaves like a table you can filter, sort and join. This is a parameterized view. The output column names (customer_id, orders, revenue) become variables inside the function too, so a query that mentions a column with one of those names can become ambiguous; qualify columns with a table alias, as o.customer_id does above.

Three ways to return data

Scalar RETURNS numeric gives one value per call.
SETOF RETURNS SETOF orders gives rows shaped like a table.
TABLE RETURNS TABLE (a int, b text) declares columns inline.

Scalar goes in SELECT lists, table-returning goes in FROM.

1 / 2

Here is the live counterpart of top_customers: the same query with the threshold written into a CTE, so you can change one number and rerun it.

SQL The query behind top_customers

Press Run (or Ctrl + Enter) to see the result here.

Customers 1 and 3 have at least three orders; customer 2 has only two and drops out. Change 3 to 5 and only customer 3 remains. A function is this pattern with a real, typed parameter instead of a params CTE.

Volatility: telling the planner what a function does

Every function has a volatility category. It is a promise you make, and the planner trusts it.

Category Promise Examples
IMMUTABLE Same arguments always give the same result, forever, and the function reads no tables. lower(text), abs(int), your tax formula
STABLE Same arguments give the same result within one statement. It may read tables, but it changes nothing. now(), a function that looks up a rate in a table
VOLATILE (the default) The result may change at any moment, and the function may change data. random(), nextval(), anything that writes

The category matters for three reasons:

  1. Indexes. An expression index such as CREATE INDEX ON users (lower(email)) requires an IMMUTABLE function. If the result could change, the stored index entries would become wrong.
  2. Optimization. The planner evaluates a STABLE or IMMUTABLE function once per statement when its arguments are constants, and it can use it as an index condition. A VOLATILE function is evaluated once per row, and the planner cannot reorder or skip it.
  3. Correctness. Only a VOLATILE function sees the effects of commands run earlier in the same statement.

The mistake to avoid is lying. Marking a function IMMUTABLE when it reads a table, or depends on the time zone setting, can produce stale results and corrupt indexes, because PostgreSQL does not check your promise. If in doubt, use STABLE, and use VOLATILE for anything that writes. A conservative label costs a little speed; a wrong one can cost correctness.

You can also add STRICT (also written RETURNS NULL ON NULL INPUT), which makes the function return NULL without running whenever any argument is NULL, and PARALLEL SAFE, which allows the planner to use the function in parallel query plans when it touches no shared state.

Inlining: why plain SQL functions are fast

PostgreSQL can inline a simple SQL function: it replaces the call with the function's expression before planning. A query such as WHERE price_with_tax(price) > 50 is then planned as if you had typed round(price * 1.20, 2) > 50 yourself. Statistics, indexes and join order all work as usual.

A scalar SQL function is a candidate for inlining when it is a single SELECT expression with no side effects, it is not SECURITY DEFINER, it has no SET clauses, and it is not declared STRICT unless the planner can prove the inlined form behaves the same. A function that PostgreSQL cannot inline is a black box: it is called once per row, and the planner guesses at the number of rows it returns. PL/pgSQL functions (the next lesson, PL/pgSQL Basics) are never inlined, which is one reason to prefer SQL functions for simple logic.

Set-returning SQL functions can be inlined too, when the body is a simple SELECT and the function is STABLE or IMMUTABLE. In that case a WHERE clause outside the function can be pushed down into it, which gives you a view-like performance with a parameter.

Several statements and writing functions

A SQL function body may contain several statements separated by semicolons. Only the last one produces the result, which makes it easy to wrap a write and return what changed:

SQL
CREATE FUNCTION archive_old_orders(cutoff date)
RETURNS bigint
LANGUAGE sql
AS $$
  INSERT INTO orders_archive
  SELECT * FROM orders WHERE placed_on < cutoff;

  WITH gone AS (
    DELETE FROM orders WHERE placed_on < cutoff RETURNING 1
  )
  SELECT count(*) FROM gone;
$$;

SELECT archive_old_orders(date '2024-01-01');

The function is VOLATILE by default, which is right: it writes. Both statements run in the transaction of the caller, so if the caller rolls back, the archive and the delete are both undone.

Procedures and CALL

A procedure is declared with CREATE PROCEDURE and invoked with CALL. It differs from a function in two ways: it has no return value you can use in a query, and it may run transaction control (COMMIT and ROLLBACK) as long as it is called outside an explicit transaction block. That makes procedures the right tool for long jobs that should commit in batches.

SQL
CREATE PROCEDURE clear_expired_sessions(batch_size int DEFAULT 1000)
LANGUAGE plpgsql
AS $$
DECLARE
  deleted int;
BEGIN
  LOOP
    DELETE FROM sessions
    WHERE id IN (
      SELECT id FROM sessions WHERE expires_at < now() LIMIT batch_size
    );
    GET DIAGNOSTICS deleted = ROW_COUNT;
    EXIT WHEN deleted = 0;
    COMMIT;            -- allowed in a procedure
  END LOOP;
END;
$$;

CALL clear_expired_sessions(500);

Transaction control needs a procedural language such as PL/pgSQL, so this example anticipates the next lesson. A pure SQL procedure can still be useful for a fixed series of statements; it just cannot commit in the middle. Use a function when you want to read the result in a query, and a procedure when you want to perform an action, especially one with batch commits.

Dropping, replacing and finding functions

CREATE OR REPLACE FUNCTION changes the body without dropping the function, which keeps grants and dependent objects intact. You cannot change the argument types or the return type this way; for that, drop the old function first. Functions are identified by name and argument types, so price_with_tax(numeric) and price_with_tax(numeric, numeric) are two distinct functions (overloading). To remove one you write DROP FUNCTION price_with_tax(numeric, numeric);.

In psql, the meta-command \df lists functions, and \sf name prints the source of one. Because the body of a classic function is stored as text, you can also read it from the catalog with pg_get_functiondef('price_with_tax'::regproc).

Overloading, CREATE OR REPLACE and DROP FUNCTION in depth

A function's identity is its schema, name and list of argument types, not its name alone. That has practical consequences for creating, replacing and removing functions.

Overloading and how PostgreSQL picks one

You may create several functions with the same name as long as their argument types differ:

SQL
-- PostgreSQL
CREATE FUNCTION describe(i integer) RETURNS text
  LANGUAGE sql IMMUTABLE AS $$ SELECT 'integer ' || i $$;

CREATE FUNCTION describe(t text) RETURNS text
  LANGUAGE sql IMMUTABLE AS $$ SELECT 'text ' || t $$;

CREATE FUNCTION describe(n numeric, scale integer) RETURNS text
  LANGUAGE sql IMMUTABLE AS $$ SELECT 'numeric ' || round(n, scale) $$;

SELECT describe(42);           -- integer 42
SELECT describe('hello');      -- text hello
SELECT describe(3.14159, 2);   -- numeric 3.14

When you call describe(...), PostgreSQL resolves the call in a fixed order:

  1. Collect every function with that name that accepts the number of arguments given (taking default values into account).
  2. If one has exactly the argument types of the call, use it.
  3. Otherwise keep the candidates to which the arguments can be implicitly cast, and prefer the ones with the most exact matches, then ones whose types belong to the same type category as the inputs, and in that category the preferred type (for numbers, double precision).
  4. Untyped literals such as 'hello' start out as type unknown; where candidates remain, string literals are resolved as text.
  5. If a single best candidate is left, it is called. If not, you get an error such as function describe(boolean) does not exist, you might need to add explicit type casts, or function ... is not unique.

Because resolution can surprise you, overloads should mean the same thing with different inputs (like round for numeric and for double precision), not do different jobs. Two habits keep you safe: cast arguments when in doubt (describe(42::bigint)), and avoid mixing overloads with default parameter values, since f(a int) and f(a int, b int DEFAULT 0) make the call f(1) ambiguous.

Resolution also depends on the search_path, because the same name in two schemas gives two different candidate sets. Qualify the schema in security-sensitive code.

CREATE OR REPLACE: what can change

CREATE OR REPLACE FUNCTION replaces the body of the function with the same name and argument types. It keeps the function's identity, so grants, ownership, triggers and views that call it stay in place. But PostgreSQL protects dependants and therefore refuses to change:

  • the return type (error: cannot change return type of existing function),
  • the names of input parameters (error: cannot change name of input parameter),
  • the OUT parameters.

Everything else may change: the body, the language, the volatility (IMMUTABLE, STABLE, VOLATILE), STRICT, COST and so on. Changing the argument types does not replace anything: it creates a new overload next to the old one, which is a common source of "I updated the function but nothing changed" confusion. Check what exists:

SQL
-- PostgreSQL
SELECT p.oid::regprocedure   AS signature,
       p.prorettype::regtype AS returns,
       CASE p.provolatile WHEN 'i' THEN 'immutable'
                          WHEN 's' THEN 'stable'
                          ELSE 'volatile' END AS volatility
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public' AND p.proname = 'describe';

In psql, \df describe shows the same list and \sf describe(integer) prints the source of one signature.

Dropping a function

Because of overloading, DROP FUNCTION needs the argument types unless the name is unique:

SQL
-- PostgreSQL
DROP FUNCTION describe(integer);                 -- one overload
DROP FUNCTION describe;                          -- allowed only if exactly one function has this name
DROP FUNCTION IF EXISTS describe(text), describe(numeric, integer);
DROP FUNCTION price_with_tax(numeric) CASCADE;   -- also drops views, triggers, defaults that use it
DROP ROUTINE report_totals(date);                -- function or procedure, whichever it is

Without CASCADE, PostgreSQL refuses to drop a function something depends on, and lists the dependants. Read that list before adding CASCADE. To change a return type safely, use a migration: create the new function, move the callers, then drop the old one (see Schema Migrations). Use ALTER FUNCTION describe(integer) RENAME TO describe_int; to rename, and OWNER TO or SET SCHEMA to change owner or schema.

Volatility is part of the contract

When you overload or replace a function, restate the volatility: IMMUTABLE (same inputs always give the same output, usable in index expressions), STABLE (constant within one statement) or VOLATILE (anything, the default). A function declared IMMUTABLE that actually reads a table can produce wrong results from cached values and corrupt an expression index. The planner trusts what you write.

The program below imitates these resolution steps for the describe overloads, so you can see which candidate wins for different calls. It is a simplified model of the documented rules, not the real resolver:

Python Which overload gets called?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

When a call of your own is ambiguous or picks the wrong overload, an explicit cast such as 'hello'::text or 42::bigint is always the answer.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which volatility category must a function have to be used in an expression index?

    An index stores computed results, so the function must always return the same value for the same input. Only IMMUTABLE makes that promise. STABLE results can change between statements.
  2. 2. What does RETURNS TABLE (id int, name text) let you do?

    A table-returning function is a set-returning function. You call it in the FROM clause, and you can join, filter and sort its output like any other row source.
  3. 3. How is a procedure different from a function?

    CALL runs a procedure as a statement, and procedures may control transactions when they are not called inside an explicit transaction block. Functions run inside the calling statement and cannot commit.
  4. 4. Why can the planner optimize a simple SQL function better than a PL/pgSQL function?

    Inlining pastes the function body into the calling statement, so the planner sees the real expression, with real statistics and indexes. PL/pgSQL functions are opaque to the planner.

Summary

  • A SQL function wraps a query or expression, takes typed parameters (with defaults) and is declared with LANGUAGE sql and a dollar-quoted body.
  • RETURNS SETOF and RETURNS TABLE make parameterized views that you call in FROM.
  • Volatility (IMMUTABLE, STABLE, VOLATILE) is a promise to the planner: index expressions need IMMUTABLE, and a false promise can corrupt results.
  • Simple SQL functions are inlined, so they cost almost nothing at run time.
  • Procedures are run with CALL, return no value and can commit in batches.
  • SQLite has no stored functions, so the live examples used views and CTEs; the CREATE FUNCTION blocks are read-only.

Next: PL/pgSQL Basics, where you will add variables, branches and loops to your functions.