skip to content

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.