skip to content

PL/pgSQL Programming

PL/pgSQL wraps SQL in blocks with variables, loops, SELECT INTO STRICT, RAISE and EXCEPTION handlers. Interviewers probe STRICT and FOUND semantics and injection-safe dynamic SQL.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

In PL/pgSQL, how does SELECT INTO behave with and without STRICT when zero or several rows match, and what does FOUND report?

level: middleimportance: must knowfreq 62%

answer

  1. first row or NULLs
  2. exactly one row, or an error
  3. P0002 versus P0003
  4. EXECUTE leaves it alone
  5. RETURNING INTO and many rows

basics

~10 s

Without STRICT, SELECT INTO takes the first row or sets NULLs and never errors; FOUND says whether a row came back. INTO STRICT requires exactly one row, raising NO_DATA_FOUND (P0002) or TOO_MANY_ROWS (P0003) otherwise.

solid answer

~40 s

Plain `SELECT ... INTO v` assigns the first row and discards the rest, or sets `v` to NULL when nothing matches; it never errors, and I check `FOUND`. `INTO STRICT` demands exactly one row: zero raises `NO_DATA_FOUND` (`P0002`), several raise `TOO_MANY_ROWS` (`P0003`). So for a lookup by key where absence is a bug, like the source member of a points transfer, I write `STRICT`; where absence is a normal case I test `IF NOT FOUND`. `FOUND` is set by `SELECT INTO`, `PERFORM`, DML, `FETCH`/`MOVE`, loops and `RETURN QUERY`, but not by `EXECUTE`, so after dynamic SQL I read `GET DIAGNOSTICS ... = ROW_COUNT`. And `UPDATE ... RETURNING ... INTO` errors on several rows even without `STRICT`.

code

sql · 27 lines
sql
CREATE FUNCTION transfer_points(p_from bigint, p_to bigint, p_points bigint)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  v_balance loyalty_member.points_balance%TYPE;
BEGIN
  -- exactly one source member, or P0002 / P0003
  SELECT points_balance INTO STRICT v_balance
    FROM loyalty_member
   WHERE member_id = p_from
     FOR UPDATE;

  IF v_balance < p_points THEN
    RAISE EXCEPTION 'member % has % points, needs %', p_from, v_balance, p_points;
  END IF;

  UPDATE loyalty_member SET points_balance = points_balance - p_points
   WHERE member_id = p_from;
  UPDATE loyalty_member SET points_balance = points_balance + p_points
   WHERE member_id = p_to;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'target member % does not exist', p_to;
  END IF;
  RETURN v_balance - p_points;
END;
$$;

go deeper

for a junior

Recall the two outcomes: without STRICT you get the first row or NULLs; with STRICT anything but one row is an error. Know that FOUND exists and when to test it.

for a middle

Explain the full table of zero, one and many rows, the P0002 and P0003 codes, which statements set FOUND, and why EXECUTE needs GET DIAGNOSTICS ROW_COUNT instead.

for a senior

Show how a NULL from a missed lookup slips past an IF guard and corrupts data, and choose STRICT or FOUND per call site. Mention print_strict_params and extra_errors for diagnosis.

for a principal

Treat row-count contracts as an API decision: which lookups are invariants that must fail loudly, and how the error codes they raise become part of what callers handle.

## Two forms of SELECT INTO In PL/pgSQL, `SELECT ... INTO target` assigns a query result to variables. (It is unrelated to SQL's own `SELECT INTO`, which creates a table.) The `STRICT` keyword decides what happens when the query does not return exactly one row: | Rows returned | `SELECT ... INTO v` | `SELECT ... INTO STRICT v` | |---|---|---| | 0 | `v` set to NULL; `FOUND` false | error `NO_DATA_FOUND`, SQLSTATE `P0002`, "query returned no rows" | | 1 | `v` assigned; `FOUND` true | `v` assigned; `FOUND` true | | more than 1 | first row assigned, the rest discarded; `FOUND` true | error `TOO_MANY_ROWS`, SQLSTATE `P0003`, "query returned more than one row" | Without `ORDER BY`, "the first row" is whichever row the plan happens to produce, so a non-strict `INTO` over a non-unique predicate is **nondeterministic**. ## Why the non-strict form is a trap in a points transfer Take a function that debits one loyalty member and credits another. It reads `SELECT points_balance INTO v_balance FROM loyalty_member WHERE member_id = p_from;`. If `p_from` does not exist, `v_balance` becomes NULL. The guard `IF v_balance < p_points THEN RAISE ...` compares NULL and gets NULL, which an `IF` treats as false, so execution carries on: the debit `UPDATE` matches no row, the credit succeeds, and points have been created from nothing. Two correct fixes: 1. Write `INTO STRICT` when exactly one row is the contract, as in a lookup by primary key. The missing member becomes an error with a precise SQLSTATE that a caller can trap. 2. Keep the plain form and test `FOUND` straight after it (`IF NOT FOUND THEN RAISE EXCEPTION ...`) when "no row" is a case you want to handle yourself. Testing `v_balance IS NULL` is not equivalent: it cannot tell "no such member" from "member exists with a NULL balance". ## What sets FOUND `FOUND` is a boolean local to each function call. It starts false and changes only on these statements: - `SELECT INTO`: true if a row was assigned; a successful `STRICT` always sets it true. - `PERFORM`: true if the query produced at least one row. - `INSERT`, `UPDATE`, `DELETE`, `MERGE`: true if at least one row was affected. - `FETCH` and `MOVE` on a cursor: true if a row was returned or the cursor moved. - `FOR` and `FOREACH` loops: set when the loop exits, true if it iterated at least once; inside the body the loop statement itself leaves `FOUND` alone. - `RETURN QUERY` and `RETURN QUERY EXECUTE`: true if the query returned a row. Notably, **`EXECUTE` does not change `FOUND`**. After dynamic SQL, read `GET DIAGNOSTICS v_rows = ROW_COUNT;` instead. `ROW_COUNT`, a `bigint`, is the number of rows processed by the most recent SQL command, and `EXECUTE` does update it. Read it right after the command it describes, because any other SQL command in between replaces the count. ## DML with RETURNING INTO is strict about "many" `UPDATE ... RETURNING member_id INTO v_id`, and the same for `INSERT`, `DELETE` and `MERGE`, raises "query returned more than one row" when several rows are affected **even without STRICT**, because there is no `ORDER BY` with which to pick one. On zero rows it raises only if `STRICT` is written; otherwise `v_id` is NULL and `FOUND` is false. ## Making failures easier to diagnose - `#print_strict_params on` as the first line of a function body, or `plpgsql.print_strict_params` (default `off`, applied to functions compiled afterwards), adds the statement's parameter values to the error's `DETAIL` when a `STRICT` requirement fails. - `plpgsql.extra_warnings` and `plpgsql.extra_errors` (both default `none`) accept `too_many_rows`, which reports a non-strict `INTO` whose query returned several rows, and `strict_multi_assignment`, which reports a target list whose size does not match the columns. The manual encourages setting them to `all` in development and test.

  • Why is `IF v_balance IS NULL` a weaker check than `IF NOT FOUND` after a non-strict SELECT INTO?
    A missing row and an existing row whose column is NULL both leave `v_balance` NULL. `FOUND` reports whether a row was assigned at all, so it separates "member does not exist" from "balance is NULL". With `STRICT` the question disappears: a missing row raises `NO_DATA_FOUND` before the next line runs.
  • A caller wants a friendlier message for a missing member; how do you keep STRICT but customise the error?
    Wrap the lookup in a nested block with `EXCEPTION WHEN no_data_found THEN RAISE EXCEPTION 'member % does not exist', p_from;`, optionally with `USING ERRCODE` and `HINT`. Keep it out of hot loops: a block with an `EXCEPTION` clause is markedly more expensive to enter and exit, so there a plain `INTO` plus `IF NOT FOUND THEN RAISE` is cheaper.
  • How do you see which argument values made a STRICT lookup fail in production?
    Enable `print_strict_params`, either per function with the `#print_strict_params on` option at the top of the body or globally with `plpgsql.print_strict_params = on` (default `off`, applied to functions compiled afterwards). The error then carries a `DETAIL` listing the statement's parameter values, which reaches the server log with the error.

  • SELECT INTO without STRICT raises an error when no row matches.
  • STRICT only rejects zero rows; extra rows are silently discarded.
  • An EXECUTE'd UPDATE sets FOUND just like a static UPDATE does.
  • UPDATE ... RETURNING INTO quietly keeps the first row when several are affected.
  • Checking the target variable IS NULL is the same as checking NOT FOUND.
open as a page

In PL/pgSQL, how do you build a dynamic statement whose table name and filter value come from parameters without opening an SQL injection hole?

level: seniorimportance: must knowfreq 58%

basics

~20 s

Use EXECUTE with format(): put table or column names in with %I, pass every data value as a $n placeholder bound by USING, and never concatenate raw input or use %s. EXECUTE plans each time and does not set FOUND.

open as a page

In PostgreSQL's PL/pgSQL, how is a block structured, and what do %TYPE and %ROWTYPE give your variable declarations?

level: juniorimportance: should knowfreq 52%

basics

~20 s

A PL/pgSQL block is an optional label and DECLARE section, then BEGIN statements, an optional EXCEPTION section and END; BEGIN/END only group code. %TYPE copies a column's or variable's type, %ROWTYPE a table's whole row type.

open as a page

In PL/pgSQL, when an EXCEPTION clause catches an error, what happens to data changes and local variables, and where do RAISE and SQLSTATE fit?

level: middleimportance: should knowfreq 48%

basics

~20 s

When an EXCEPTION handler catches an error, PostgreSQL rolls back the block's database changes but keeps local variables and earlier statements. RAISE EXCEPTION throws an error, P0001 by default or a chosen SQLSTATE that handlers match.

open as a page

In PostgreSQL, when does PL/pgSQL parse and prepare the SQL inside a function, and what production surprises does that statement caching cause?

level: seniorimportance: nice to knowfreq 30%

basics

~20 s

PL/pgSQL compiles a function body on its first call in each session and prepares each SQL statement the first time it executes, then reuses it. So errors in unexecuted branches surface late, names cannot be variables, and literals like 'now' freeze.

open as a page