SQL Functions, PL/pgSQL Functions, Procedures, Volatility, Parallel Safety, and Cost

Learning outcomes

ServiceHub has repeated business calculations, compact reporting queries, and a maintenance operation that would be safer if one database API owned the transaction sequence. PostgreSQL offers several server-side routine forms, but they participate in the optimizer, privilege system, MVCC snapshots, transaction rules, and parallel execution. A function declaration is therefore more than syntax: attributes such as volatility and parallel safety are promises to PostgreSQL about what the routine can do.

1. Build a disposable ServiceHub routine dataset

DROP TABLE IF EXISTS app.ch18_rate_config CASCADE;DROP TABLE IF EXISTS app.ch18_work_order CASCADE;SET ROLE servicehub_owner;CREATE TABLE app.ch18_rate_config (  rate_name text PRIMARY KEY,  rate numeric(8,4) NOT NULL CHECK (rate >= 0));INSERT INTO app.ch18_rate_config VALUES('labor_markup', 1.1500),('priority_multiplier', 1.2500);CREATE TABLE app.ch18_work_order (  work_order_id bigint PRIMARY KEY,  status text NOT NULL CHECK (status IN ('queued','assigned','completed')),  labor_minutes integer NOT NULL CHECK (labor_minutes >= 0),  hourly_rate numeric(10,2) NOT NULL CHECK (hourly_rate >= 0),  opened_at timestamptz NOT NULL DEFAULT clock_timestamp(),  closed_at timestamptz);INSERT INTO app.ch18_work_order VALUES(18001,'assigned',90,120.00,clock_timestamp(),NULL),(18002,'completed',45,100.00,clock_timestamp()-interval '2 hours',clock_timestamp()),(18003,'queued',0,110.00,clock_timestamp(),NULL);RESET ROLE;

The tables are intentionally small. This lesson is about observable semantics, not a benchmark. Every routine is schema-qualified so later security lessons do not depend on a caller's search_path.

2. SQL-language functions are good for declarative expressions

CREATE OR REPLACE FUNCTION app.ch18_labor_cost(  p_minutes integer,  p_hourly_rate numeric)RETURNS numericLANGUAGE SQLIMMUTABLEPARALLEL SAFESTRICTRETURN (p_minutes / 60.0) * p_hourly_rate;SELECT work_order_id,       app.ch18_labor_cost(labor_minutes, hourly_rate) AS labor_costFROM app.ch18_work_orderORDER BY work_order_id;

This routine depends only on its arguments, so IMMUTABLE is a valid promise. STRICT means PostgreSQL can return SQL NULL without executing the function when any argument is NULL. PARALLEL SAFE is reasonable because the routine performs only immutable arithmetic.

The SQL-standard-style body (RETURN expression) is parsed at definition time and records dependencies more reliably than hiding SQL inside a string literal. PL/pgSQL becomes useful when procedural control flow, exception handling, local variables, or dynamic commands are required.

3. Set-returning functions have a planner cardinality contract

CREATE OR REPLACE FUNCTION app.ch18_orders_by_status(p_status text)RETURNS TABLE (  work_order_id bigint,  labor_minutes integer,  hourly_rate numeric)LANGUAGE SQLSTABLEPARALLEL SAFEROWS 100RETURN  SELECT w.work_order_id, w.labor_minutes, w.hourly_rate  FROM app.ch18_work_order AS w  WHERE w.status = p_status;SELECT * FROM app.ch18_orders_by_status('completed');SELECT p.oid::regprocedure,       p.provolatile,       p.proparallel,       p.procost,       p.prorowsFROM pg_proc AS pWHERE p.oid = 'app.ch18_orders_by_status(text)'::regprocedure;

ROWS 100 tells the optimizer how many rows to expect from each set-returning call; it does not cap output at 100 and does not prove the function will return 100 rows. PostgreSQL defaults to 1000 rows when a set-returning function lacks an explicit estimate. Revisit the estimate if the real distribution changes enough to affect join planning.

4. PL/pgSQL adds procedural structure—not a different transaction universe

CREATE OR REPLACE FUNCTION app.ch18_close_work_order(p_id bigint)RETURNS app.ch18_work_orderLANGUAGE plpgsqlVOLATILEPARALLEL UNSAFEAS $$DECLARE  v_row app.ch18_work_order;BEGIN  UPDATE app.ch18_work_order  SET status = 'completed',      closed_at = clock_timestamp()  WHERE work_order_id = p_id    AND status <> 'completed'  RETURNING * INTO v_row;  IF NOT FOUND THEN    RAISE EXCEPTION USING      ERRCODE = 'P1801',      MESSAGE = format('work order %s cannot be closed', p_id),      HINT = 'Verify the id and current status.';  END IF;  RETURN v_row;END$$;SELECT (app.ch18_close_work_order(18001)).*;

The function writes data, so it must be VOLATILE and parallel-unsafe. It runs inside the transaction of the SQL statement that called it; it cannot independently COMMIT or ROLLBACK that surrounding transaction.

5. Procedures are invoked with CALL and can own transaction boundaries in the right context

CREATE OR REPLACE PROCEDURE app.ch18_complete_zero_labor_queue()LANGUAGE plpgsqlSECURITY INVOKERAS $$DECLARE  v_id bigint;BEGIN  FOR v_id IN    SELECT work_order_id    FROM app.ch18_work_order    WHERE status = 'queued' AND labor_minutes = 0    ORDER BY work_order_id  LOOP    UPDATE app.ch18_work_order    SET status = 'completed', closed_at = clock_timestamp()    WHERE work_order_id = v_id;    COMMIT;  END LOOP;END$$;CALL app.ch18_complete_zero_labor_queue();-- Restore the shared Chapter 18 lab state for the next lesson.UPDATE app.ch18_work_orderSET status = 'queued', closed_at = NULLWHERE work_order_id = 18003;

A procedure has no RETURNS value and is invoked with CALL. PostgreSQL permits transaction control inside procedures only when the call stack and invocation context allow it. In particular, a top-level CALL that is not inside an explicit transaction can commit and automatically start a new transaction. The following standalone UPDATE deliberately restores work order 18003 after the demonstration so Lesson 2 begins from a reproducible queued state; it is not part of the procedure's transaction.

BEGIN;CALL app.ch18_complete_zero_labor_queue();-- Expected when procedure reaches COMMIT:-- ERROR: invalid transaction terminationROLLBACK;

A SECURITY DEFINER procedure also cannot execute transaction-control statements, and adding a routine-level SET clause imposes the same restriction. If a workflow needs privileged execution and mid-procedure commits, redesign the privilege/transaction boundary instead of trying to combine incompatible properties.

6. Volatility is a promise to the optimizer

VOLATILE is the safe default. STABLE promises one consistent result for the same arguments within a statement and disallows database modifications. IMMUTABLE promises that the result depends only on arguments forever, which allows constant folding and reuse across prepared plans.

CREATE OR REPLACE FUNCTION app.ch18_rate_bad(p_name text)RETURNS numericLANGUAGE plpgsqlIMMUTABLEPARALLEL SAFEAS $$DECLARE  v_rate numeric;BEGIN  SELECT rate INTO v_rate  FROM app.ch18_rate_config  WHERE rate_name = p_name;  RETURN v_rate;END$$;PREPARE ch18_rate_plan ASSELECT app.ch18_rate_bad('labor_markup') AS rate;EXECUTE ch18_rate_plan; -- initially 1.1500UPDATE app.ch18_rate_configSET rate = 1.3000WHERE rate_name = 'labor_markup';EXECUTE ch18_rate_plan;-- A prepared plan may keep the folded old constant because IMMUTABLE was a false promise.DEALLOCATE ch18_rate_plan;

The defect is not “prepared statements are stale.” The defect is the false immutable declaration. A table lookup can change independently of the function arguments.

CREATE OR REPLACE FUNCTION app.ch18_rate(p_name text)RETURNS numericLANGUAGE SQLSTABLEPARALLEL SAFERETURN (  SELECT c.rate  FROM app.ch18_rate_config AS c  WHERE c.rate_name = p_name);SELECT app.ch18_rate('labor_markup');

STABLE is appropriate for a read-only lookup whose result should be consistent within one statement but may change across statements. If a function performs writes or needs to see changes that vary during a scan, use VOLATILE.

7. Parallel safety is separate from volatility

PostgreSQL does not inspect arbitrary user-defined code deeply enough to prove parallel safety, so user-defined functions default to PARALLEL UNSAFE. Mark a routine safe only after checking everything it calls and every backend-local facility it touches. Database writes, sequence access, transaction state changes, and persistent setting changes are unsafe; temporary tables, cursors, prepared statements, and certain session-local state make routines restricted to the leader.

SELECT n.nspname,       p.proname,       pg_get_function_identity_arguments(p.oid) AS args,       p.prokind,       p.provolatile,       p.proparallel,       p.procost,       p.prorows,       l.lannameFROM pg_proc AS pJOIN pg_namespace AS n ON n.oid = p.pronamespaceJOIN pg_language AS l ON l.oid = p.prolangWHERE n.nspname = 'app'  AND p.proname LIKE 'ch18_%'ORDER BY p.proname;

prokind='f' identifies functions while prokind='p' identifies procedures. Catalog review is valuable in code review because routine attributes can materially change planner behavior without changing the function body.

8. COST is measured in planner units, not milliseconds

ALTER FUNCTION app.ch18_rate(text) COST 20;SELECT p.oid::regprocedure, p.procostFROM pg_proc AS pWHERE p.oid = 'app.ch18_rate(text)'::regprocedure;ALTER FUNCTION app.ch18_rate(text) COST 100;

Function cost is expressed in units of cpu_operator_cost; for set-returning functions it is per returned row. It is a planner hint, not a timing SLA. Measure actual workload behavior before changing cost/rows estimates, and restore experimental values when the diagnostic is finished.

9. Checkpoint

Authoritative references

Routines, trigger timing, privileges, and planner promises are version-sensitive. These PostgreSQL 18 primary sources define the behavior used in this lesson.

• PostgreSQL 18 — CREATE FUNCTION
• PostgreSQL 18 — Function Volatility Categories
• PostgreSQL 18 — Parallel Safety
• PostgreSQL 18 — User-Defined Procedures
• PostgreSQL 18 — CREATE PROCEDURE