MOVE

Repositions a cursor without fetching data.

Category: cursors

Syntax

MOVE [<direction>] IN <cursor>;

Description

## Overview MOVE repositions a cursor without retrieving any data. It is used to skip rows or navigate to a specific position before fetching. ## Behavior - The cursor must be open. Moving a closed cursor raises a "Cursor not open" error. - MOVE NEXT advances the cursor by one position. MOVE PRIOR moves back one position. - MOVE FIRST and MOVE LAST jump to the first and last rows respectively. - MOVE ABSOLUTE n positions the cursor at the nth row (1-based). Internally, the value is converted to 0-based indexing. - MOVE RELATIVE n moves the cursor forward or backward by n positions from its current location. - MOVE FORWARD n advances by n rows. MOVE BACKWARD n retreats by n rows. - Unlike FETCH, MOVE never assigns data to variables or adds rows to the result set. - The FOUND variable is not updated by MOVE in the current implementation. ## Differences from PostgreSQL - PostgreSQL updates FOUND after MOVE (true if the move landed on a valid row). DeltaForge does not currently update FOUND after MOVE. Use FETCH to test whether the new position is valid. - Since results are fully materialized, MOVE operations are O(1) position changes rather than physical row traversals.

Parameters

Name	Type	Description
`direction`		Specify the movement direction: NEXT, PRIOR, FIRST, LAST, ABSOLUTE n, RELATIVE n, FORWARD n, or BACKWARD n.
`cursor`		Identify the open cursor to reposition.

Examples

DO $$
DECLARE
  cur CURSOR FOR SELECT id, name FROM employees ORDER BY id;
  rec RECORD;
BEGIN
  OPEN cur;
  MOVE FORWARD 10 IN cur;
  FETCH cur INTO rec;
  PRINT 'Row 11:', rec.name;
  CLOSE cur;
END;
$$;

DO $$
DECLARE
  cur SCROLL CURSOR FOR SELECT * FROM products ORDER BY price;
  rec RECORD;
BEGIN
  OPEN cur;
  MOVE LAST IN cur;
  FETCH PRIOR FROM cur INTO rec;
  PRINT 'Second most expensive:', rec.name;
  CLOSE cur;
END;
$$;

DO $$
DECLARE
  cur CURSOR FOR SELECT id FROM large_table;
  rec RECORD;
BEGIN
  OPEN cur;
  MOVE ABSOLUTE 100 IN cur;
  FETCH cur INTO rec;
  PRINT 'Row 101:', rec.id;
  MOVE RELATIVE -50 IN cur;
  FETCH cur INTO rec;
  PRINT 'Row 51:', rec.id;
  CLOSE cur;
END;
$$;

Pitfalls

FOUND is not updated by MOVE. Use a subsequent FETCH to determine whether the cursor is positioned on a valid row.
MOVE ABSOLUTE uses 1-based positioning. Passing 0 positions the cursor before the first row, not on the first row.
Using directional MOVE (PRIOR, BACKWARD) on a non-SCROLL cursor may work in the current implementation but should not be relied upon.