OPEN

Opens a cursor for fetching.

Category: cursors

Syntax

OPEN <cursor> [(<args>)];
OPEN <cursor> FOR <query>;
OPEN <cursor> FOR EXECUTE <sql_string>;

Description

## Overview OPEN executes the cursor's query and materializes the result set, making it available for row-by-row retrieval via FETCH. Three forms are supported: opening a bound cursor (optionally with arguments), opening an unbound cursor with a static query, and opening an unbound cursor with a dynamic EXECUTE query. ## Behavior - For bound cursors (declared with a query), OPEN executes the bound query. If the cursor declaration includes parameters, argument values are substituted for $1, $2, etc. in the query text. - For unbound cursors (declared without a query), OPEN requires a FOR clause specifying the query. The FOR EXECUTE variant evaluates a string expression as dynamic SQL. - The query is executed through DeltaForge's routed SQL execution engine (DataFusion for SELECTs). All result rows are materialized into Arrow RecordBatches and stored on the cursor state. - The cursor position is reset to "before first row". The first FETCH NEXT returns row 1. - The `scrollable` flag is set from the optional scroll parameter (defaults to false). - Opening an already-open cursor raises a "Cursor already open" error. Close the cursor first before reopening. - After closing and reopening a cursor, the query re-executes, producing a fresh result set. ## Differences from PostgreSQL - All results are fully materialized at OPEN time. PostgreSQL uses lazy server-side cursors that stream results on demand. For very large result sets, this may consume significant memory. - PostgreSQL supports OPEN with SCROLL/NO SCROLL modifiers at open time. DeltaForge accepts these but the scroll flag primarily affects the FETCH direction validation.

Parameters

Name	Type	Description
`cursor`		Specify the declared cursor variable to open.
`args`		Pass argument values to a parameterized bound cursor.
`query`		Provide a SQL query for unbound cursors, or use FOR EXECUTE with a dynamic SQL string.

Examples

DO $$
DECLARE
  emp_cursor CURSOR FOR SELECT id, name FROM employees;
  rec RECORD;
BEGIN
  OPEN emp_cursor;
  FETCH emp_cursor INTO rec;
  PRINT rec.name;
  CLOSE emp_cursor;
END;
$$;

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

DO $$
DECLARE
  dyn_cursor REFCURSOR;
  rec RECORD;
  table_name TEXT := 'products';
BEGIN
  OPEN dyn_cursor FOR EXECUTE 'SELECT * FROM ' || table_name || ' ORDER BY id LIMIT 5';
  LOOP
    FETCH dyn_cursor INTO rec;
    EXIT WHEN NOT FOUND;
    PRINT rec.id;
  END LOOP;
  CLOSE dyn_cursor;
END;
$$;

DO $$
DECLARE
  cur CURSOR FOR SELECT id FROM orders;
  rec RECORD;
BEGIN
  OPEN cur;
  FETCH cur INTO rec;
  PRINT 'First order:', rec.id;
  CLOSE cur;
  OPEN cur;
  FETCH cur INTO rec;
  PRINT 'First order again:', rec.id;
  CLOSE cur;
END;
$$;

Pitfalls

Opening an already-open cursor raises an immediate error. Always close before reopening.
All query results are materialized into memory at OPEN time. Large result sets may exhaust available memory. Use WHERE clauses or LIMIT to constrain results.
Unbound cursors require a FOR clause at OPEN time. Omitting it when the cursor has no bound query raises "Unbound cursor without query".
Parameterized cursor arguments use simple string substitution ($1, $2). This is not SQL injection safe for user-provided values; validate inputs.