Skip to content
Tutorials Center
My learning

Topics

Lesson 51 of 90 ยท Functions, PL/pgSQL and Triggers

PL/pgSQL: Cursors, Dynamic SQL and Errors

Go deeper with PL/pgSQL: build safe dynamic SQL with EXECUTE and format(), handle errors with EXCEPTION blocks, use cursors, and avoid SECURITY DEFINER traps.

The basics of PL/pgSQL cover variables, branches and loops. Real programs need more: a table name that is only known at run time, a failure that should be caught and turned into a clear message, a result set too large to hold in memory, or a function that runs with someone else's privileges. This lesson covers those four jobs: dynamic SQL, exception handling, cursors and the safety rules for SECURITY DEFINER.

What runs live here. The in-browser engine is SQLite, which has no PL/pgSQL, so every PostgreSQL block below is read-only. You can still practise the underlying ideas live: SQLite's printf has the same job as PostgreSQL's format, with %w for identifiers and %Q for literals, and savepoints show what an EXCEPTION block does to a transaction.

Dynamic SQL with EXECUTE

A normal statement inside a PL/pgSQL function has a fixed shape: the table and column names are written in the source, and only values can change (through variables). When the structure itself varies, for example one function that counts rows in any table, you build the statement as a string and run it with EXECUTE:

SQL
CREATE FUNCTION row_count(p_schema text, p_table text)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  result bigint;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I.%I', p_schema, p_table)
    INTO result;
  RETURN result;
END;
$$;

SELECT row_count('public', 'orders');

Two things matter here. EXECUTE ... INTO puts the single result row into a variable. And the statement is assembled with format(), not with the || concatenation operator. That is the most important habit of this lesson.

Why format() and never string concatenation

If you glue text together by hand, a malicious or merely unusual value changes the meaning of the query. This is SQL injection, and it happens inside the database exactly as it does in application code. format() provides placeholders that quote correctly:

Placeholder Treats the argument as Example result for O'Neil
%I an identifier (quotes it only if needed) "O'Neil"
%L a literal string (quotes it, NULL becomes NULL) 'O''Neil'
%s plain text, no quoting at all O'Neil

Use %I for table and column names, %L for values you must embed, and avoid %s for anything that comes from outside the function. Even better, pass values as real parameters with USING, which never go through text at all:

SQL
EXECUTE format('SELECT count(*) FROM %I WHERE status = $1', p_table)
  INTO result
  USING p_status;

Here $1 is a genuine bind parameter, so p_status cannot change the structure of the query, and the planner can reuse the plan. Only names (tables, columns, schemas) need format(); values should travel through USING. The general rules for safe queries are covered again in SQL Injection and Safe Queries in PostgreSQL.

Dynamic SQL has costs: the statement is parsed and planned on every call, nothing is checked until it runs, and mistakes appear as run-time errors. Use it only when the structure truly varies.

You can run the same kind of quoting live. SQLite's printf (also spelled format) offers %w to double quotes inside an identifier and %Q to produce a quoted literal. This TryIt builds statements as text, so you can see what the injected string becomes without executing it:

SQL Quoting identifiers and literals safely

Press Run (or Ctrl + Enter) to see the result here.

The first four columns come back correctly quoted: the doubled quote in weird "name" becomes "", and O'Neil becomes 'O''Neil'. The last query shows how plain concatenation turns the value x' OR '1'='1 into extra SQL. That is exactly what format('%L', ...) and USING prevent.

Handling errors with EXCEPTION blocks

Any block may end with an EXCEPTION section. If a statement in the block fails, PostgreSQL rolls back everything the block did (it implicitly wraps the block in a savepoint) and runs the matching handler:

SQL
CREATE FUNCTION add_user(p_email text)
RETURNS boolean
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO users (email) VALUES (p_email);
  RETURN true;
EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'already registered: %', p_email;
    RETURN false;
  WHEN check_violation OR not_null_violation THEN
    RAISE EXCEPTION 'invalid user data for %', p_email;
END;
$$;

Handlers match by condition name (unique_violation, foreign_key_violation, division_by_zero, no_data_found, ...) or by five-character SQLSTATE code ('23505'). WHEN OTHERS catches nearly everything, but it should be rare, since it can hide bugs. Inside a handler you can re-raise the original error with a bare RAISE;.

Because the block works like a savepoint, the failed insert above leaves no trace, and the surrounding transaction can continue. That is also the cost: entering a block with an EXCEPTION clause starts a subtransaction, which is measurably slower and, in loops that run millions of times, can exhaust subtransaction resources. Use an exception block when you need to handle an error, not as a default wrapper around every function.

Learning more about the error

Inside a handler, SQLSTATE and SQLERRM hold the code and message. For the full picture, use GET STACKED DIAGNOSTICS:

SQL
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS
    v_state   = RETURNED_SQLSTATE,
    v_message = MESSAGE_TEXT,
    v_detail  = PG_EXCEPTION_DETAIL,
    v_hint    = PG_EXCEPTION_HINT,
    v_table   = TABLE_NAME,
    v_context = PG_EXCEPTION_CONTEXT;
  INSERT INTO error_log (state, message, detail, context)
  VALUES (v_state, v_message, v_detail, v_context);
  RAISE;   -- log it, then let the caller see it

Note that the log insert would normally be rolled back along with the failed transaction when the error propagates. If you need the log to survive, write it somewhere outside the transaction, such as a separate connection with dblink, or log through RAISE LOG, which goes to the server log.

Your own errors can carry data for callers: RAISE EXCEPTION 'out of stock' USING ERRCODE = 'P0001', DETAIL = 'product 7', HINT = 'reorder'. Codes starting with P0 are reserved for PL/pgSQL, and applications can pick their own five-character codes for business rules.

The same "undo just this part" behaviour exists in plain SQL as savepoints. This example plays the part of a handler: it tries a failing insert, rolls back to the savepoint, and carries on.

SQL A savepoint as an exception block

Press Run (or Ctrl + Enter) to see the result here.

Only Ana and Cleo exist: the work between SAVEPOINT and ROLLBACK TO vanished while the rest of the transaction survived. A PL/pgSQL EXCEPTION block does this automatically for the whole block when an error occurs.

Cursors and row-by-row processing

Normally a query returns all its rows at once. A cursor lets a function read a result a few rows at a time, which matters when the result is too big to hold in memory or when you want to stop early. PL/pgSQL's FOR rec IN SELECT ... loop already uses a cursor internally and fetches in batches, so for most jobs you do not need to manage one yourself.

When you do want explicit control, declare, open, fetch and close:

SQL
DO $$
DECLARE
  cur CURSOR FOR SELECT id, total FROM orders ORDER BY id;
  rec record;
  running numeric := 0;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO rec;
    EXIT WHEN NOT FOUND;
    running := running + rec.total;
    EXIT WHEN running > 1000;
  END LOOP;
  CLOSE cur;
  RAISE NOTICE 'stopped at order % with running total %', rec.id, running;
END;
$$;

A function can also hand a cursor to its caller by returning refcursor; the caller then runs FETCH statements in the same transaction. In applications, a cursor is how a driver streams a huge result (a "server-side cursor" or "named cursor") instead of loading it into memory at once.

Cursors are the right tool for streaming, but they are not a way to avoid set-based thinking. If you loop over a cursor just to update each row, one UPDATE ... FROM is almost always faster.

Row types and composite results

record and %ROWTYPE variables hold whole rows, and you can pass them around. A function can accept a table's row type directly (FUNCTION full_name(u users)) and be called as full_name(users.*) in a query. A function can also return SETOF users or a composite you define yourself. Assigning to a field of a NEW or OLD record is the basis of trigger functions, which the next lesson covers.

SECURITY DEFINER and the search_path trap

By default a function runs with the privileges of whoever calls it (SECURITY INVOKER). Adding SECURITY DEFINER makes it run with the privileges of whoever owns it. That is powerful: a low-privilege role can be allowed to perform one carefully designed action, such as inserting into a table it cannot touch directly.

It is also dangerous, for a reason that surprises many people: name resolution uses search_path, and the caller controls search_path. A malicious user can create their own schema with a function named like one your code calls, point search_path at it, and have your privileged function run their code with the owner's rights.

The safe pattern is to pin the path inside the function and fully qualify what matters:

SQL
CREATE FUNCTION transfer_funds(p_from int, p_to int, p_amount numeric)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
BEGIN
  UPDATE public.accounts SET balance = balance - p_amount WHERE id = p_from;
  UPDATE public.accounts SET balance = balance + p_amount WHERE id = p_to;
END;
$$;

REVOKE ALL ON FUNCTION transfer_funds(int, int, numeric) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION transfer_funds(int, int, numeric) TO app_user;

Three rules follow. First, always add SET search_path (listing pg_catalog first and your own trusted schema after it, never a schema others can write to). Second, remember that new functions are executable by PUBLIC by default, so REVOKE that and GRANT only to the roles that need it. Third, validate arguments inside the function, because the caller is untrusted even though the code is trusted.

A safe dynamic query

Names Quote tables and columns with format('%I').
Values Pass values with USING, as real parameters.
Privileges Run with the least rights the job needs.

Never build SQL by joining raw user text.

1 / 2

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which placeholder does format() provide for quoting a table name?

    %I quotes an identifier only when needed. %L quotes a literal value, and %s inserts text with no quoting, which is unsafe for outside input.
  2. 2. What happens to the work done inside a block when an exception handler in that block catches an error?

    A block with an EXCEPTION clause is wrapped in an implicit savepoint. When an error is caught, everything the block changed is undone and the outer transaction continues.
  3. 3. Why should a SECURITY DEFINER function set its own search_path?

    The caller controls their own search_path. Without a pinned path, a malicious schema could shadow a function or table and run code with the owner's privileges.
  4. 4. What is the safest way to include a user-supplied status value in a dynamic query?

    USING sends the value as a real bind parameter, so it can never change the statement structure. Use format only for names.

Summary

  • EXECUTE runs a statement built at run time. Build names with format('%I'), and pass values with USING; never join raw text.
  • An EXCEPTION block catches errors by condition name or SQLSTATE, rolls back the block's work, and lets the outer transaction continue. It costs a subtransaction, so use it deliberately.
  • GET STACKED DIAGNOSTICS reads the message, detail, hint and context of the caught error, and a bare RAISE; re-raises it.
  • Cursors stream big results a few rows at a time; most loops already use one for you.
  • SECURITY DEFINER functions need a pinned SET search_path, a REVOKE from PUBLIC and validated arguments.
  • SQLite offers printf('%w'/'%Q') and savepoints as live stand-ins; the PostgreSQL code stayed read-only.

Next: Triggers, where functions start running automatically when data changes.