PL/pgSQL Functions Tutorial
On this page
What PL/pgSQL functions are for
By the end of this lesson you'll have a small set of functions for an order system. One computes a price with tax, one returns a customer's stats, one returns a list of rows, and one inserts safely when duplicates are possible. Along the way you'll learn when a plain SQL function is enough and when you need PL/pgSQL, PostgreSQL's built-in procedural language.
Set up two tables first:
CREATE TABLE customers (
customer_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
full_name text NOT NULL
);
CREATE TABLE orders (
order_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id int NOT NULL REFERENCES customers,
total numeric(10,2) NOT NULL
);
INSERT INTO customers (email, full_name) VALUES ('ana@example.com', 'Ana Silva'), ('ben@example.com', 'Ben Ortiz');
INSERT INTO orders (customer_id, total) VALUES (1, 120), (1, 80), (2, 45);Step 1: Start with a plain SQL function
If the logic fits in one SQL expression, write a LANGUAGE sql function. It's the simplest kind, and the planner can often inline it into the calling query, which makes it the fastest too.
CREATE FUNCTION add_tax(amount numeric, rate numeric)
RETURNS numeric
LANGUAGE sql IMMUTABLE
RETURN round(amount * (1 + rate), 2);
SELECT add_tax(100, 0.08); -- 108.00The IMMUTABLE label is explained in Step 4. The RETURN expression body is the SQL-standard short form, available since PostgreSQL 14.
Step 2: Add variables and branching with PL/pgSQL
Once you need variables, IF statements, loops or error handling, switch to LANGUAGE plpgsql. The body is a block of DECLARE (variables), BEGIN (statements) and END:
CREATE FUNCTION customer_tier(p_customer_id int)
RETURNS text
LANGUAGE plpgsql STABLE
AS $$
DECLARE
v_spent numeric;
BEGIN
SELECT coalesce(sum(total), 0) INTO v_spent
FROM orders WHERE customer_id = p_customer_id;
IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
RAISE EXCEPTION 'Customer % does not exist', p_customer_id;
ELSIF v_spent >= 150 THEN
RETURN 'gold';
ELSIF v_spent > 0 THEN
RETURN 'regular';
ELSE
RETURN 'new';
END IF;
END;
$$;
SELECT full_name, customer_tier(customer_id) FROM customers; -- Ana gold, Ben regularA few things to notice:
- The
$$ ... $$quotes the body, so you don't have to escape single quotes inside it. SELECT ... INTO v_spentstores a query result in a variable.RAISE EXCEPTIONstops the function with an error.%placeholders are filled from the arguments that follow.- The
p_andv_prefixes aren't required, but they matter. If a variable has the same name as a column used in a query, PostgreSQL raises an "ambiguous" error. Prefixes avoid the clash.
You can type a variable after a column with customers.email%TYPE, or after a whole row with customers%ROWTYPE. The function then follows schema changes automatically.
PL/pgSQL also has loops. FOR r IN SELECT ... LOOP ... END LOOP walks a query result row by row, and FOREACH x IN ARRAY ... walks an array. Reach for them only when you can't express the work as one set-based SQL statement. A loop that updates rows one at a time is far slower than a single UPDATE.
Step 3: Return rows, not just one value
A function can return a whole result set and be used like a table. Declare the output columns with RETURNS TABLE and produce them with RETURN QUERY:
CREATE FUNCTION top_customers(p_limit int)
RETURNS TABLE (customer_id int, full_name text, spent numeric)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
RETURN QUERY
SELECT c.customer_id, c.full_name, sum(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY sum(o.total) DESC
LIMIT p_limit;
END;
$$;
SELECT * FROM top_customers(5);The output column names (customer_id, full_name) also act as variables inside the function. That's why the query qualifies every column with an alias, c.customer_id, to avoid the ambiguity mentioned above.
Step 4: Volatility: IMMUTABLE, STABLE and VOLATILE
Every function has a volatility label. It's a promise to the planner about what the function does, and the planner optimizes based on it:
| Label | Promise | Example |
|---|---|---|
IMMUTABLE | Same inputs always give the same output, and it never reads tables | add_tax |
STABLE | Reads the database, but returns the same answer for the same inputs within one statement | customer_tier |
VOLATILE (default) | Can change data, or return something different on every call | Anything using random() or doing an INSERT |
Only IMMUTABLE functions can be used in index expressions (Day 23). The danger is lying. If you mark a function IMMUTABLE but it reads a table, PostgreSQL may cache a stale answer or build an index that's silently wrong. When in doubt, choose the less strict label.
Step 5: Exception handling in PL/pgSQL
An EXCEPTION section catches errors from its block. Catch the specific condition you expect, not everything:
CREATE FUNCTION add_customer(p_email text, p_name text)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO customers (email, full_name) VALUES (p_email, p_name);
RETURN 'inserted';
EXCEPTION
WHEN unique_violation THEN
RETURN 'skipped: email already exists';
END;
$$;
SELECT add_customer('ana@example.com', 'Ana Again'); -- skipped: email already existsWhen an error is caught, PostgreSQL undoes whatever the block did, like an automatic savepoint (Day 12). That safety has a cost, so keep EXCEPTION blocks small and around the statement that can fail. Catching OTHERS hides bugs you'd want to know about. If you use it, at least log the error with SQLERRM. For this particular case, INSERT ... ON CONFLICT DO NOTHING (Day 6) is simpler still.
Functions vs procedures
A function returns a value and runs inside the caller's transaction. A procedure, created with CREATE PROCEDURE and run with CALL, can COMMIT partway through. That makes procedures the tool for long batch jobs that should save progress as they go:
CREATE PROCEDURE purge_small_orders(p_batch int)
LANGUAGE plpgsql
AS $$
BEGIN
LOOP
DELETE FROM orders WHERE order_id IN (
SELECT order_id FROM orders WHERE total < 50 LIMIT p_batch);
EXIT WHEN NOT FOUND;
COMMIT; -- allowed in a procedure, not in a function
END LOOP;
END;
$$;
CALL purge_small_orders(1000);One security note. Functions run with the caller's privileges by default. SECURITY DEFINER makes them run with the owner's privileges instead, which is useful for tightly controlled operations. If you use it, set the search_path on the function (SET search_path = pg_catalog, public) for the reasons covered on Day 14.
Check yourself
IMMUTABLE promised the result never depends on table data, so PostgreSQL may have reused earlier results. Mark it STABLE, which allows reading tables.
A parameter, variable or RETURNS TABLE column has the same name as a column in a query inside the function. Qualify the column with a table alias, or rename the variable with a prefix like p_ or v_.
Make it a procedure that deletes in batches and COMMITs after each one. Locks are released after every batch, and the work done so far survives a failure.
When it's a single query or expression. SQL functions are simpler, and the planner can often inline them into the calling query. Use PL/pgSQL when you need variables, loops, branching or exception handling.
A function returns a value and runs inside the caller's transaction, so it can't commit. A procedure is called with CALL, returns no value in the usual way, and can COMMIT or ROLLBACK partway through, which makes it suitable for batch jobs that process work in chunks.