FOR (query results)

Iterates over rows returned by a query.

Category: control-flow

Syntax

FOR <record> IN <query> LOOP
  <statements>
END LOOP;

Description

## Overview The FOR query loop executes a SQL query and iterates over each result row, assigning column values to a record variable or a set of scalar variables. It is the primary mechanism for processing query results row-by-row in PL/pgSQL blocks. ## Behavior - The query is executed once via the routed SQL execution engine (DataFusion for SELECTs, Delta for DML). All result batches are materialized before iteration begins. - A new variable scope is pushed. The loop target (record or scalar variables) is declared within this scope if not already present in an outer scope. If it exists in an outer scope, the existing variable is reused. - For Record targets, each row is converted to a RecordValue with named fields matching the query's column aliases. Fields are accessed via dot notation (e.g., `rec.column_name`). - For Scalar targets (comma-separated variable names), each column maps positionally to the corresponding variable. Type normalization (Utf8View to Utf8, etc.) is applied automatically. - Three query sources are supported: static SQL strings, dynamic EXECUTE with optional USING parameters, and cursor references. When the query text is a simple identifier matching a declared cursor, the executor automatically treats it as a cursor loop. - EXIT and CONTINUE work within the loop. CONTINUE skips to the next row. ## Differences from PostgreSQL - Query results are fully materialized into Arrow RecordBatches before iteration starts. PostgreSQL uses a cursor-based approach that can stream results lazily. For very large result sets, consider using explicit cursors with FETCH to control memory usage. - The implicit cursor-detection heuristic checks whether the query text is a simple identifier. Ambiguous names that match both a cursor and a table may be misrouted. Use explicit OPEN/FETCH/CLOSE for clarity in such cases.

Parameters

Name	Type	Description
`record`		Declare or reuse a record variable that receives each row's columns as fields.
`query`		Provide a SQL query, dynamic EXECUTE expression, or cursor reference that produces rows.

Examples

DO $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT id, name FROM employees LOOP
    PRINT rec.id, rec.name;
  END LOOP;
END;
$$;

DO $$
DECLARE
  rec RECORD;
  total_salary NUMERIC := 0;
BEGIN
  FOR rec IN SELECT department, sum(salary) as dept_total FROM employees GROUP BY department LOOP
    PRINT rec.department, ':', rec.dept_total;
    total_salary := total_salary + rec.dept_total;
  END LOOP;
  PRINT 'Grand total:', total_salary;
END;
$$;

DO $$
DECLARE
  rec RECORD;
  tbl_name TEXT;
BEGIN
  FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' LOOP
    tbl_name := rec.table_name;
    EXECUTE 'SELECT count(*) FROM ' || tbl_name;
    PRINT tbl_name, 'processed';
  END LOOP;
END;
$$;

DO $$
DECLARE
  emp_id INT;
  emp_name TEXT;
BEGIN
  FOR emp_id, emp_name IN SELECT id, name FROM employees WHERE active = true LOOP
    PRINT emp_id, emp_name;
  END LOOP;
END;
$$;

DO $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN EXECUTE 'SELECT * FROM ' || 'orders' || ' WHERE amount > $1' USING 100 LOOP
    PRINT rec.id, rec.amount;
  END LOOP;
END;
$$;

Pitfalls

All query results are materialized in memory before iteration begins. Very large result sets may cause out-of-memory conditions. Use LIMIT or explicit cursor-based fetching for large datasets.
Column aliases in the query determine the record field names. Without aliases, generated names (e.g., `column0`) may be unpredictable. Always use explicit column aliases.
Scalar target variables must appear in the same positional order as the query columns. A mismatch between variable count and column count silently assigns NULL to extra variables.