Stored Procedures and Functions

Every query so far was sent from a client, executed, and forgotten. A stored routine is code that lives inside the database. You call it by name, it runs next to the data, and every client gets the same behavior.

PostgreSQL has two kinds, and the difference matters. A function returns a value and is used inside queries. A procedure is invoked with CALL, returns nothing (or output parameters), and can control transactions.

A first function#

The order-total calculation again, saved once:

CREATE OR REPLACE FUNCTION order_total(p_order_id INTEGER)
RETURNS NUMERIC
LANGUAGE sql
STABLE
AS $$
    SELECT COALESCE(SUM(quantity * unit_price), 0)
    FROM order_items
    WHERE order_id = p_order_id;
$$;

SELECT order_total(9);

 order_total
-------------
      607.50

Pieces worth naming:

• $$ ... $$ is dollar quoting. The body is a string, and $$ lets you write single quotes inside without escaping each one.
• LANGUAGE sql means the body is plain SQL. Simple, and the planner can often inline it into the calling query, which makes it fast.
• p_order_id uses a p_ prefix for parameters. It is a habit that prevents a parameter from colliding with a column of the same name, a real source of bugs.

A function can be used anywhere an expression can:

SELECT order_id, status, order_total(order_id) AS total
FROM orders
WHERE customer_id = 1;

Functions that return rows#

RETURNS TABLE lets a function act like a parameterized view.

CREATE OR REPLACE FUNCTION customer_orders(p_customer_id INTEGER)
RETURNS TABLE (order_id INTEGER, order_date DATE, status VARCHAR, total NUMERIC)
LANGUAGE sql
STABLE
AS $$
    SELECT o.order_id, o.order_date, o.status, order_total(o.order_id)
    FROM orders o
    WHERE o.customer_id = p_customer_id
    ORDER BY o.order_date;
$$;

SELECT * FROM customer_orders(1);

 order_id | order_date |  status   | total
----------+------------+-----------+--------
        1 | 2024-02-01 | delivered | 148.00
        2 | 2024-03-15 | delivered | 334.00
       10 | 2024-05-02 | shipped   | 244.00

PL/pgSQL: when you need logic#

Plain SQL cannot branch, loop or raise errors with custom messages. PL/pgSQL, PostgreSQL's procedural language, adds variables, IF, loops and exception handling around ordinary SQL statements.

A realistic case: cancelling an order must check its state, return stock to the shelves and mark the order, all or nothing.

CREATE OR REPLACE PROCEDURE cancel_order(p_order_id INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
    v_status VARCHAR(20);
BEGIN
    SELECT status INTO v_status
    FROM orders
    WHERE order_id = p_order_id
    FOR UPDATE;                       -- lock the order row while we work

    IF NOT FOUND THEN
        RAISE EXCEPTION 'order % does not exist', p_order_id;
    END IF;

    IF v_status NOT IN ('pending', 'paid') THEN
        RAISE EXCEPTION 'order % is % and can no longer be cancelled', p_order_id, v_status;
    END IF;

    UPDATE products p
    SET stock_qty = p.stock_qty + oi.quantity
    FROM order_items oi
    WHERE oi.order_id = p_order_id
      AND oi.product_id = p.product_id;

    UPDATE orders SET status = 'cancelled' WHERE order_id = p_order_id;
END;
$$;

CALL cancel_order(8);        -- paid order: succeeds
CALL cancel_order(1);        -- delivered: raises an error

ERROR:  order 1 is delivered and can no longer be cancelled

The whole body runs inside the caller's transaction. When RAISE EXCEPTION fires, everything the procedure did so far rolls back with it. Order 8 is a paid order for the Standing Desk, so the first call restores one unit of stock and flips the status, atomically.

The FOR UPDATE on the first select is what keeps two concurrent cancellations from both restocking. It is the row-locking tool from the previous module, used where it counts.

Syntax you will use constantly#

DECLARE
    v_count INTEGER := 0;
    v_total NUMERIC;
BEGIN
    SELECT COUNT(*) INTO v_count FROM orders WHERE customer_id = 1;   -- store a result

    IF v_count > 2 THEN ... ELSIF v_count = 0 THEN ... ELSE ... END IF;

    FOR r IN SELECT order_id FROM orders LOOP ... END LOOP;           -- iterate rows

    RAISE NOTICE 'count is %', v_count;                                -- debug output
    GET DIAGNOSTICS v_rows = ROW_COUNT;                                -- rows touched by last DML
    RETURN QUERY SELECT ...;                                           -- in set-returning functions
END;

NOT FOUND is a special variable that tells you whether the last SELECT INTO or DML statement touched any rows.

Handling errors#

An EXCEPTION block catches named errors.

BEGIN
    INSERT INTO customers (full_name, email, city, country)
    VALUES ('New Person', 'ben@example.com', 'Pune', 'India');
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'email already registered, skipping';
END;

Know the cost. A block with an EXCEPTION clause opens a subtransaction internally (like a savepoint). That is fine occasionally and expensive in a tight loop, where thousands of subtransactions add up.

Function or procedure#

Rule of thumb: if it computes something, write a function. If it performs an action, possibly with several transactions such as processing a large backlog in batches, write a procedure. Procedures arrived in PostgreSQL 11.

Volatility#

You declare how a function behaves, and the planner trusts you.

Lying causes quiet bugs. A function marked IMMUTABLE that reads a table will be evaluated once and cached, so you get stale answers. The correct label also matters for indexes: you can only build an expression index on an IMMUTABLE function.

Security notes#

A routine runs with the privileges of the caller by default. SECURITY DEFINER runs it with the privileges of its owner, which lets a restricted user perform one controlled action. It is also a well-known foot-gun. Always pair it with an explicit SET search_path = public, pg_temp on the function, or a malicious user can shadow the objects it uses.

Inside routines, never build SQL by concatenating strings from parameters. The next lessons and the security lesson cover safe dynamic SQL.

Other databases#

The concept is universal and the dialect is not. MySQL uses CREATE PROCEDURE with DELIMITER changes in the client. SQL Server uses T-SQL with @variables and CREATE PROCEDURE ... AS BEGIN. Oracle uses PL/SQL, which PL/pgSQL deliberately resembles. The logic ports. The syntax needs rewriting.

Should the logic live in the database?#

Arguments for:

• The rule is enforced for every client, in every language.
• One round trip replaces many.
• Multi-step operations run next to the data and inside one transaction.
• It can form a controlled permission boundary.

Arguments against:

• Versioning, code review, testing and debugging tools are weaker than in your application language.
• Logic splits across two places, and people forget one of them.
• Scaling database CPU is harder than scaling application servers.
• It locks you toward one vendor.

A balanced position: keep data integrity rules and operations that must be atomic in the database. Keep business workflows, calculations with complicated rules and anything that changes often in application code. Do not put an entire application in stored procedures, and do not forbid yourself one when it is the cleanest tool.

Common mistakes#

Marking functions IMMUTABLE that read tables. You get cached, wrong answers.

Unprefixed parameter names. A parameter called status and a column called status leads to ambiguity errors or, worse, silently using the wrong one.

Row-by-row loops in PL/pgSQL for set work. A single UPDATE beats a loop of a thousand UPDATEs every time.

Exception blocks inside hot loops. Each one is a subtransaction.

Forgetting that a function runs in the caller's transaction. It cannot commit on its own, and an error rolls back the caller's work too.

Practice#

1. Write a function customer_spend(p_customer_id) that returns total non-cancelled spend. customer_spend(1) should return 726.00.
2. Write a procedure restock(p_product_id, p_qty) that rejects non-positive quantities, adds stock, and reports an error if the product does not exist. Test all three paths.
3. Create cancel_order as above. Cancel order 12 (pending). Check that stock for products 9 and 5 each increased by one.
4. Write a set-returning function top_products(p_limit) that returns the best sellers by units sold in non-cancelled orders. top_products(3) should start with Wireless Mouse (7).
5. Mark a function that reads products as IMMUTABLE, call it in a query, change the data, and call it again. Describe what could go wrong with an index.