Cursor Declaration

Declares a cursor for row-by-row result processing.

Category: declarations

Syntax

DECLARE
  <name> [SCROLL] CURSOR [(<params>)] FOR <query>;

Description

## Overview Cursor declarations define named cursors in the DECLARE section of a PL/pgSQL block. A cursor provides row-by-row access to a query result set, enabling incremental processing without materializing the entire result into a single variable. ## Behavior - Bound cursors include a FOR query clause. The query text is stored on the CursorDeclaration AST node and executed when OPEN is called. - Unbound cursors (declared as REFCURSOR or without a FOR clause) receive their query at OPEN time via OPEN cursor FOR query. - Parameterized cursors accept typed parameters in parentheses. At OPEN time, argument values are substituted for $1, $2, etc. in the bound query via string replacement. - The SCROLL modifier allows bidirectional traversal (FETCH PRIOR, FIRST, LAST, ABSOLUTE, RELATIVE). Without SCROLL, only forward movement is guaranteed. - NO SCROLL can be specified explicitly to deny backward movement. - Cursor declarations are processed during block entry, before the BEGIN. The cursor is registered in the variable store's cursor map but is not yet open. - Cursor names are scoped to their enclosing block. Inner blocks can shadow outer cursors. ## Differences from PostgreSQL - PostgreSQL cursors can be passed between functions as REFCURSOR values. DeltaForge does not currently support passing cursors across function/procedure boundaries. - In PostgreSQL, a non-SCROLL cursor may still allow backward fetching in some implementations. DeltaForge tracks the scroll flag but does not strictly enforce forward-only semantics in the current version.

Parameters

Name	Type	Description
`name`		Assign a unique name to the cursor within the block scope.
`params`		Define typed parameters (e.g., dept_id INT) that are substituted into the query at OPEN time.
`scroll`		Enable SCROLL to allow bidirectional cursor movement (PRIOR, FIRST, LAST, ABSOLUTE, RELATIVE).
`query`		Bind a SQL query to the cursor. Omit to create an unbound cursor that receives its query at OPEN time.

Examples

DO $$
DECLARE
  emp_cursor CURSOR FOR SELECT id, name, salary FROM employees ORDER BY salary DESC;
  rec RECORD;
BEGIN
  OPEN emp_cursor;
  FETCH emp_cursor INTO rec;
  PRINT 'Highest paid:', rec.name, rec.salary;
  CLOSE emp_cursor;
END;
$$;

DO $$
DECLARE
  dept_cursor CURSOR (dept_id INT) FOR
    SELECT name, hire_date FROM employees WHERE department_id = dept_id;
  rec RECORD;
BEGIN
  OPEN dept_cursor(10);
  LOOP
    FETCH dept_cursor INTO rec;
    EXIT WHEN NOT FOUND;
    PRINT rec.name, rec.hire_date;
  END LOOP;
  CLOSE dept_cursor;
END;
$$;

DO $$
DECLARE
  product_cursor CURSOR SCROLL FOR SELECT id, name, price FROM products ORDER BY id;
  rec RECORD;
BEGIN
  OPEN product_cursor;
  FETCH LAST FROM product_cursor INTO rec;
  PRINT 'Last product:', rec.name;
  FETCH FIRST FROM product_cursor INTO rec;
  PRINT 'First product:', rec.name;
  CLOSE product_cursor;
END;
$$;

DO $$
DECLARE
  dyn_cursor REFCURSOR;
  rec RECORD;
BEGIN
  OPEN dyn_cursor FOR SELECT * FROM audit_log WHERE event_date = CURRENT_DATE;
  LOOP
    FETCH dyn_cursor INTO rec;
    EXIT WHEN NOT FOUND;
    PRINT rec.event_type, rec.message;
  END LOOP;
  CLOSE dyn_cursor;
END;
$$;

Pitfalls

Parameterized cursor arguments use string substitution, not parameterized queries. Ensure argument values are safe and correctly typed.
An unbound cursor (REFCURSOR) that is never opened with OPEN...FOR will raise an error on FETCH.
Cursor results are fully materialized at OPEN time. For large result sets, prefer LIMIT or WHERE filters in the bound query.