Functions, Procedures, and PL/pgSQL

Functions, procedures, and PL/pgSQL let PostgreSQL run application logic next to the data it protects. The practical outcome is not simply shorter application code. It is the ability to package a calculation, validation rule, data-changing workflow, or privileged operation as a named database object with typed inputs, controlled permissions, and predictable behavior inside PostgreSQL’s executor.

In this server-side design section, the topic matters because stored code sits at the same layer as tables, constraints, indexes, transactions, and roles. A well-designed function can remove duplicate business rules from several applications. A poorly designed one can hide expensive queries, bypass row security, or make transaction behavior surprising. The goal is to know when stored code is the right tool and how to shape it so callers understand its contract.

Functions and Procedures in Plain Language

A PostgreSQL function is a named routine that returns a value, a row, a set of rows, or void. It can be called from SQL expressions, queries, triggers, check constraints in limited cases, indexes if it is immutable, and application code. A procedure is a named routine invoked with CALL. It is meant for commands and workflows rather than expression evaluation, and unlike a function it can perform transaction control when called in the correct top-level context.

PL/pgSQL is PostgreSQL’s built-in procedural language. It adds variables, conditional branches, loops, exception blocks, and structured access to SQL statements. SQL functions are often better for one declarative query. PL/pgSQL becomes useful when the routine needs multiple statements, intermediate values, conditional behavior, custom errors, or careful handling of data-changing steps.

How PostgreSQL Stores and Runs Routines

Functions and procedures are recorded in system catalogs, most visibly pg_proc. The signature is the routine name plus input argument types, which means PostgreSQL can overload routines with the same name when their argument types differ. The catalog also records the language, return type, volatility, parallel-safety marking, security mode, cost estimate, and the routine body.

When a caller invokes a routine, PostgreSQL resolves the name through the active search_path, checks argument types, checks execute permission, and then runs the function manager for the routine’s language. For PL/pgSQL, PostgreSQL parses the routine body when it is first executed in a session and caches execution plans for SQL statements where possible. This gives PL/pgSQL good performance for repeated calls, but it also means changes to table structure, search path, or parameter-sensitive plans can affect behavior in ways that plain application SQL makes more visible.

Volatility markings are part of the contract. IMMUTABLE means the same inputs always produce the same output and the result does not depend on table contents, time, configuration, or random values. STABLE means the result is stable within a single statement, such as a lookup that reads tables. VOLATILE is the default and covers routines that change data, call now() in a time-sensitive way, use randomness, or depend on changing state. The planner may fold, reorder, or cache calls based on these markings, so labeling a function as more stable than it really is can produce wrong answers.

Syntax Anatomy

A routine definition starts with CREATE FUNCTION or CREATE PROCEDURE, a schema-qualified name, typed parameters, and a body. Function definitions also declare a return type. Attributes after the body describe execution behavior: language, volatility, strictness, security mode, leakproof status for privileged internal use, parallel safety, cost, and estimated rows for set-returning functions.

PL/pgSQL bodies use dollar quoting, commonly $$, so SQL strings inside the body do not require constant escaping. A typical body has an optional DECLARE section followed by BEGIN and END. Parameters can be referenced by name. SELECT ... INTO assigns query output to variables. RETURN sends back a scalar or row, RETURN QUERY appends rows to a set result, and RAISE EXCEPTION stops execution with a controlled error.

Example 1: A Scalar Function

This first example calculates an order total from line items. The function is STABLE because it reads tables but does not modify them, and its answer should not change during one SQL statement. STRICT means PostgreSQL returns null without executing the body if p_order_id is null.

CREATE SCHEMA IF NOT EXISTS lesson_plpgsql;

CREATE TABLE IF NOT EXISTS lesson_plpgsql.order_items (
    order_id bigint NOT NULL,
    sku text NOT NULL,
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0)
);

INSERT INTO lesson_plpgsql.order_items (order_id, sku, quantity, unit_price)
VALUES
    (1001, 'keyboard', 2, 49.50),
    (1001, 'mouse', 1, 25.00)
ON CONFLICT DO NOTHING;

CREATE OR REPLACE FUNCTION lesson_plpgsql.order_total(p_order_id bigint)
RETURNS numeric(12,2)
LANGUAGE plpgsql
STABLE
STRICT
AS $$
DECLARE
    v_total numeric(12,2);
BEGIN
    SELECT COALESCE(sum(quantity * unit_price), 0)::numeric(12,2)
    INTO v_total
    FROM lesson_plpgsql.order_items
    WHERE order_id = p_order_id;

    RETURN v_total;
END;
$$;

SELECT lesson_plpgsql.order_total(1001) AS total;

The deterministic result is 124.00: two keyboards at 49.50 plus one mouse at 25.00. The design choice is to keep the aggregation in the database, where numeric precision and table constraints are already defined. If the application needs the line-item details anyway, a view or plain query may be clearer. If many callers only need the total, the function gives one reusable contract.

Example 2: A Set-Returning Function

A set-returning function can behave like a parameterized view. The next example returns recent orders for one customer. RETURNS TABLE names the output columns, and RETURN QUERY emits a result set.

CREATE TABLE IF NOT EXISTS lesson_plpgsql.orders (
    order_id bigint PRIMARY KEY,
    customer_id bigint NOT NULL,
    status text NOT NULL CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),
    placed_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO lesson_plpgsql.orders (order_id, customer_id, status, placed_at)
VALUES
    (2001, 7, 'paid', '2026-01-10 10:00:00+00'),
    (2002, 7, 'shipped', '2026-01-12 11:00:00+00'),
    (2003, 8, 'new', '2026-01-13 09:00:00+00')
ON CONFLICT (order_id) DO NOTHING;

CREATE OR REPLACE FUNCTION lesson_plpgsql.recent_customer_orders(
    p_customer_id bigint,
    p_limit integer DEFAULT 10
)
RETURNS TABLE(order_id bigint, status text, placed_at timestamptz)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    IF p_limit < 1 OR p_limit > 100 THEN
        RAISE EXCEPTION 'p_limit must be between 1 and 100';
    END IF;

    RETURN QUERY
    SELECT o.order_id, o.status, o.placed_at
    FROM lesson_plpgsql.orders AS o
    WHERE o.customer_id = p_customer_id
    ORDER BY o.placed_at DESC
    LIMIT p_limit;
END;
$$;

SELECT order_id, status
FROM lesson_plpgsql.recent_customer_orders(7, 2);

The query returns order 2002 before 2001. The important design point is the bounded p_limit. Without it, a convenient database API can become an accidental bulk export or a slow query. The function remains readable because the actual data access is still one SQL statement.

Example 3: A Procedure for a Workflow

A procedure is better when the operation is command-shaped. This example cancels an order and writes an audit row. It does not return a table; it changes state. It also demonstrates a custom exception when the requested order cannot be changed.

CREATE TABLE IF NOT EXISTS lesson_plpgsql.order_audit (
    audit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL,
    event text NOT NULL,
    note text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE PROCEDURE lesson_plpgsql.cancel_order(
    p_order_id bigint,
    p_note text
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_changed integer;
BEGIN
    UPDATE lesson_plpgsql.orders
    SET status = 'cancelled'
    WHERE order_id = p_order_id
      AND status IN ('new', 'paid');

    GET DIAGNOSTICS v_changed = ROW_COUNT;

    IF v_changed <> 1 THEN
        RAISE EXCEPTION 'order % cannot be cancelled', p_order_id;
    END IF;

    INSERT INTO lesson_plpgsql.order_audit (order_id, event, note)
    VALUES (p_order_id, 'cancelled', p_note);
END;
$$;

CALL lesson_plpgsql.cancel_order(2001, 'customer request');

SELECT status
FROM lesson_plpgsql.orders
WHERE order_id = 2001;

After the call, order 2001 has status cancelled, and one audit row exists. If the order was already shipped, the update affects zero rows and the procedure raises an exception. The update and audit insert are part of the caller’s transaction unless the procedure is called in a context where explicit transaction control is allowed and the procedure uses it.

Design Choices and Trade-Offs

Use a SQL function for a single query or expression. Use PL/pgSQL when procedural structure makes the contract clearer. Use a procedure when callers are asking the database to perform a workflow. Use a trigger function when the logic must run automatically as a side effect of table changes, but prefer constraints for simple invariants because constraints are easier for the planner, tools, and humans to reason about.

Stored code reduces network round trips and centralizes logic, but it also moves behavior into a deployment path that application engineers may inspect less often. Function calls inside large queries can be expensive if they execute row by row. Set-based SQL inside the function is usually better than loops that query one row at a time. Mark functions accurately so the planner can make valid choices. Keep routine names schema-qualified in migrations, tests, and security-sensitive code.

Security, Performance, and Reliability

SECURITY INVOKER is the default: the function runs with the caller’s privileges. SECURITY DEFINER runs with the owner’s privileges and must be treated carefully. For definer routines, set a safe search_path, schema-qualify referenced objects, validate inputs, and grant EXECUTE only to roles that need the wrapper. Otherwise an attacker may be able to influence name resolution or reach data through a routine that was intended to be narrow.

For performance, inspect functions like any other database path. Use EXPLAIN on the SQL statements inside them, add indexes that match predicates, and watch for repeated function calls in SELECT lists or WHERE clauses over many rows. For reliability, make errors explicit. A precise RAISE EXCEPTION is easier to diagnose than a silent no-op, and checking ROW_COUNT after a critical update prevents false success.

Failure Modes and Troubleshooting

Symptom: a function sometimes returns old-looking data. Cause: it was marked IMMUTABLE even though it reads a table. Diagnose: inspect pg_proc.provolatile or use \df+ in psql. Correct: recreate it as STABLE or VOLATILE, depending on whether it changes data or observes changing state during a statement.

Symptom: a procedure reports success but no row changed. Cause: the update predicate matched nothing and the code did not check it. Diagnose: add GET DIAGNOSTICS ... ROW_COUNT or run the UPDATE predicate as a SELECT. Correct: raise a controlled exception when the expected row count is not reached.

Symptom: a security definer function works in tests but fails or reads the wrong object in production. Cause: unqualified names depend on search_path. Diagnose: inspect the function definition and caller search path. Correct: schema-qualify objects and attach SET search_path to the function definition.

Hands-On Lab

Prerequisites: a PostgreSQL database where you can create a schema, tables, functions, and procedures. Use an isolated database or a disposable schema named lesson_plpgsql.

  1. Create the schema and tables from the three examples.
  2. Create order_total, recent_customer_orders, and cancel_order.
  3. Run SELECT lesson_plpgsql.order_total(1001); and verify the result is 124.00.
  4. Run SELECT order_id, status FROM lesson_plpgsql.recent_customer_orders(7, 2); and verify that the newest order appears first.
  5. Call lesson_plpgsql.cancel_order(2001, 'customer request'), then confirm the status and audit row.
  6. Try SELECT * FROM lesson_plpgsql.recent_customer_orders(7, 1000); and verify that PostgreSQL raises the limit exception.

For cleanup, run this command after the lab:

DROP SCHEMA IF EXISTS lesson_plpgsql CASCADE;

That rollback removes the routines and tables created for the lesson. In a shared environment, review the schema contents first instead of dropping a schema that others may have reused.

Assessment Exercises

  1. You need a reusable calculation that reads a pricing table but does not modify data. Should it be IMMUTABLE, STABLE, or VOLATILE? Explain the planner risk of the wrong answer.
  2. Rewrite a row-by-row PL/pgSQL loop as one set-based UPDATE or INSERT ... SELECT. What changes in locking and performance?
  3. Design a SECURITY DEFINER function that allows support staff to cancel unpaid orders without granting direct table update privileges. Which objects must be schema-qualified?
  4. A procedure catches an exception and continues. What evidence would you require to prove it did not hide a partial failure?
  5. Given a function used in a WHERE clause over one million rows, how would you determine whether it is the source of a slow query?

Summary

PostgreSQL routines are database objects with signatures, privileges, planner-visible attributes, and transactional behavior. Functions are best for values and query-shaped APIs; procedures are best for command-shaped workflows; PL/pgSQL is useful when SQL needs procedural structure. Keep the body set-oriented where possible, mark volatility honestly, control search path in privileged routines, check row counts for critical changes, and test both success and refusal paths.