FETCH

Retrieves the next row from a cursor.

Category: cursors

Syntax

FETCH [<direction>] FROM <cursor> INTO <target>;

Directions: NEXT, PRIOR, FIRST, LAST, ABSOLUTE n, RELATIVE n, FORWARD [n], BACKWARD [n]

Description

## Overview FETCH retrieves one or more rows from an open cursor and optionally assigns the data to target variables. It is the primary mechanism for cursor-based row processing, providing fine-grained control over result set traversal. ## Behavior - The cursor must be open. Fetching from a closed cursor raises a "Cursor not open" error. - For single-row fetches with an INTO clause, the row is decomposed into the target variables. Record targets receive a RecordValue with named fields; scalar targets receive values positionally by column index. - FETCH NEXT advances the cursor by one row. The cursor starts at position "before first row", so the first FETCH NEXT returns row 1. - FETCH PRIOR, FIRST, LAST, ABSOLUTE n, and RELATIVE n require a SCROLL cursor. ABSOLUTE uses 1-based indexing (converted internally to 0-based). - FETCH FORWARD n (with a count) fetches multiple rows without an INTO clause, adding each row to the block's result set as individual RecordBatches. FETCH FORWARD without a count and with INTO behaves like FETCH NEXT. - FETCH BACKWARD n similarly fetches multiple rows in reverse. FETCH ALL (Forward(None) or Backward(None) without INTO) fetches all remaining rows. - Multi-row FETCH (FORWARD n, BACKWARD n) cannot be combined with INTO. An error is raised if both are specified. - The FOUND implicit variable is updated after each FETCH: true if a row was retrieved, false otherwise. ## Differences from PostgreSQL - Cursor results are fully materialized in memory (as Arrow RecordBatches) when the cursor is opened. PostgreSQL streams results from the server. This means memory usage is proportional to the full result set, regardless of fetch direction or count. - SCROLL behavior is tracked via a `scrollable` flag. Non-SCROLL cursors technically allow backward movement in the current implementation, but this behavior should not be relied upon.

Parameters

Name	Type	Description
`direction`		Specify the fetch direction. Defaults to NEXT. Use PRIOR, FIRST, LAST, ABSOLUTE n, RELATIVE n, FORWARD n, or BACKWARD n for positional access.
`cursor`		Identify the open cursor to fetch from.
`target`		Assign the fetched row to a record variable or a comma-separated list of scalar variables.

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.id, rec.name;
  CLOSE emp_cursor;
END;
$$;

DO $$
DECLARE
  cur CURSOR FOR SELECT id, amount FROM orders;
  v_id INT;
  v_amount NUMERIC;
BEGIN
  OPEN cur;
  LOOP
    FETCH NEXT FROM cur INTO v_id, v_amount;
    EXIT WHEN NOT FOUND;
    PRINT v_id, v_amount;
  END LOOP;
  CLOSE cur;
END;
$$;

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

DO $$
DECLARE
  cur CURSOR FOR SELECT id FROM items;
BEGIN
  OPEN cur;
  FETCH FORWARD 5 FROM cur;
  CLOSE cur;
END;
$$;

DO $$
DECLARE
  cur CURSOR FOR SELECT id, name FROM departments;
  rec RECORD;
BEGIN
  OPEN cur;
  FETCH ABSOLUTE 3 FROM cur INTO rec;
  PRINT 'Row 3:', rec.name;
  FETCH RELATIVE -1 FROM cur INTO rec;
  PRINT 'Row 2:', rec.name;
  CLOSE cur;
END;
$$;

Pitfalls

Fetching from a non-SCROLL cursor with PRIOR, FIRST, LAST, or ABSOLUTE may work but is not guaranteed. Declare the cursor as SCROLL if bidirectional traversal is needed.
Multi-row FETCH (FORWARD n) cannot use INTO. The rows are added to the result set instead. Use a LOOP with single-row FETCH INTO for per-row processing.
FOUND is updated after FETCH. Check it immediately; any subsequent SQL statement may overwrite it.
ABSOLUTE positioning is 1-based. Passing 0 or a negative value may produce unexpected results.