PL/pgSQL in depth: errors, dynamic SQL and set-returning functions

conceptThe concept

Errors: a BEGIN ... EXCEPTION WHEN condition THEN ... END block catches errors by name (unique_violation, foreign_key_violation) or SQLSTATE. Entering such a block starts a subtransaction, so the failed statement's changes are rolled back and the function can continue. GET STACKED DIAGNOSTICS reads the message, detail, constraint name and SQLSTATE.

Dynamic SQL: EXECUTE runs a string. Identifiers go through format('%I', name) (or quote_ident), literals through %L, and values preferably through EXECUTE ... USING parameters, which are never parsed as SQL.

Sets: RETURNS TABLE (...) with RETURN QUERY streams rows from a query; RETURN NEXT appends one row at a time. Simple wrappers are often better as LANGUAGE sql functions, which the planner can inline into the calling query.

Volatility: IMMUTABLE (same inputs, same output forever; required for index expressions), STABLE (constant within a statement, may read tables) and VOLATILE (the default). Mislabelling a function as IMMUTABLE produces wrong results from indexes and caches.

whyWhy it matters

Most PL/pgSQL incidents are one of three things: a broad WHEN OTHERS that swallows a real error, string concatenation that allows SQL injection, or a function marked IMMUTABLE that reads a table.

Use caseReal-world scenario

An admin function counts rows in any table named by the caller. Written with %I, a malicious table name becomes a harmless quoted identifier that simply does not exist.

Try itExamples

Run and edit these in the interactive lesson.

DO $$
DECLARE msg text; state text; detail text; cname text;
BEGIN
  INSERT INTO reviews (product_id, customer_id, rating, title, body, created_at)
  VALUES (99999, 1, 5, 'Ghost', 'No such product', now());
EXCEPTION WHEN foreign_key_violation THEN
  GET STACKED DIAGNOSTICS msg = MESSAGE_TEXT, state = RETURNED_SQLSTATE, detail = PG_EXCEPTION_DETAIL, cname = CONSTRAINT_NAME;
  RAISE NOTICE 'caught % on %: %', state, cname, detail;
END $$;

BEGIN;
CREATE FUNCTION count_where(tbl text, col text, val text) RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE n bigint;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I WHERE %I = $1', tbl, col) INTO n USING val;
  RETURN n;
END $$;
SELECT count_where('orders', 'status', 'pending') AS pending_orders;
SELECT count_where('orders; DROP TABLE orders; --', 'status', 'x');

BEGIN;
CREATE FUNCTION top_customers(p_country text, p_limit int)
RETURNS TABLE (customer text, orders bigint, spent numeric) LANGUAGE plpgsql STABLE AS $$
BEGIN
  RETURN QUERY
  SELECT c.name, count(*), sum(o.total)
  FROM customers c JOIN orders o ON o.customer_id = c.id
  WHERE c.country = p_country AND o.status <> 'cancelled'
  GROUP BY c.id ORDER BY 3 DESC LIMIT p_limit;
END $$;
SELECT * FROM top_customers('DE', 3);
ROLLBACK;

SELECT oid::regprocedure AS function, provolatile, proparallel
FROM pg_proc
WHERE oid IN ('now()'::regprocedure, 'random()'::regprocedure, 'lower(text)'::regprocedure, 'pgm.embed(text)'::regprocedure)
ORDER BY oid::regprocedure::text;

internalsUnder the hood

Exception blocks cost a subtransaction each time they are entered, which consumes a subtransaction ID when the block writes. Calling such a function per row in a big UPDATE can create millions of subtransactions; more than 64 open at once in a transaction overflows the per-backend cache and slows every snapshot on the server. Catch errors at the outermost useful level, not per row.

Avoid / preferDo this, not that

EXECUTE 'SELECT count(*) FROM ' || tbl
     || ' WHERE name = ''' || val || '''';

EXECUTE format('SELECT count(*) FROM %I WHERE name = $1', tbl)
INTO n USING val;

EXCEPTION WHEN OTHERS THEN
  RETURN NULL;

EXCEPTION WHEN unique_violation THEN
  RETURN existing_id;

PitfallsCommon mistakes

• Marking a function IMMUTABLE when it reads a table or depends on settings such as TimeZone; indexes built on it silently go stale.
• An exception block inside a per-row function, creating one subtransaction per row.
• SELECT INTO a variable without STRICT and not noticing the query matched zero or several rows.

LimitsWhen not to use it

Do not move business logic into PL/pgSQL just because you can. It is the right place for data-integrity rules, set-based maintenance and logic that must be close to the data; it is a poor place for complex branching that changes weekly, needs unit testing with mocks or calls external services.

SecuritySecurity notes

Every dynamic statement is a potential injection point; review EXECUTE calls like you would review raw SQL in application code. SECURITY DEFINER functions run with the owner's privileges, so they must set SET search_path = pg_catalog, pg_temp (or a trusted schema) to stop a caller from shadowing functions and operators with their own objects.

ChallengeTry it yourself

BEGIN;
CREATE FUNCTION safe_insert_review(p_product int, p_customer int, p_rating int) RETURNS text LANGUAGE plpgsql AS $$
DECLARE cname text;
BEGIN
  INSERT INTO reviews (product_id, customer_id, rating, title, body, created_at)
  VALUES (p_product, p_customer, p_rating, 'Test', 'Test review', now());
  RETURN 'ok';
EXCEPTION WHEN foreign_key_violation OR check_violation THEN
  GET STACKED DIAGNOSTICS cname = CONSTRAINT_NAME;
  RETURN cname;
END $$;
SELECT safe_insert_review(1, 1, 5) AS valid,
       safe_insert_review(99999, 1, 5) AS missing_product,
       safe_insert_review(1, 1, 9) AS bad_rating;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• PL/pgSQL
• Function volatility
• Triggers