MOVE
YSQL currently supports only fetching rows from a cursor consecutively in the forward direction.
See the subsection Beware Issue #6514 in the generic section Cursors. In particular, everyMOVE
variant causes the 0A000 with a message like "MOVE not supported yet".
Synopsis
Use the MOVE
statement to change the position of the current row in a cursor. See the generic section Cursors. The MOVE
statement is used jointly with the DECLARE
, FETCH
, and CLOSE
statements.
Syntax
move ::= MOVE [ move_to_one_row | move_over_many_rows ] [ FROM | IN ]
name
move_to_one_row ::= FIRST
| LAST
| ABSOLUTE int_literal
| NEXT
| FORWARD
| PRIOR
| BACKWARD
| RELATIVE int_literal
move_over_many_rows ::= ALL | FORWARD ALL
| FORWARD int_literal
| int_literal
| BACKWARD ALL
| BACKWARD int_literal
move
move_to_one_row
move_over_many_rows
Semantics
MOVE
changes the position of the current row in a cursor.
A cursor represents the current position in its result set. After declaring a cursor but before the first FETCH
or MOVE
execution, the current position is immediately before the first row.
-
The
MOVE 0
andMOVE FORWARD 0
variants leave the current position unchanged. They therefore has no practical value -
The bare
MOVE
variant, theMOVE NEXT
variant, the bareMOVE FORWARD
variant, and theMOVE FORWARD 1
variant all update the current position to one row after where if was before invoking the statement. If before executing one of theseMOVE
variants, the current position is the last row in the result set, then the current position us set to after the last row. There are no flavors of after the last row. It's a uniquely defined state so that following any number of invocations ofMOVE NEXT
in this state,MOVE PRIOR
will then fetch the last row in the result set (and update the current position to that last row.) -
The
MOVE PRIOR
variant, the bareMOVE BACKWARD
variant, and theMOVE BACKWARD 1
variant all update the current position to one row before where if was before invoking the statement. If before executing one of theseMOVE
variants, the current position is the first row in the result set, then the current position us set to before the first row. There are no flavors of before the first row. It's a uniquely defined state so that following any number of invocations ofMOVE PRIOR
in this state,MOVE NEXT
will update the current position to the first row. -
MOVE ALL
(andMOVE FORWARD ALL
) move over all the rows from the row immediately after the current position through the last row, and the cursor position is left after the last row. Of course, if whenMOVE ALL
(orMOVE FORWARD ALL
) is invoked, the current position is already after the last row, then the current position is left after the last row. -
MOVE BACKWARD ALL
moves over all the rows from the row immediately before the current position through the first row, and the cursor position is left before the first row. Of course, if whenMOVE BACKWARD ALL
is invoked, the current position is already before the first row, then the current position is left before the first row. -
The
MOVE :n
(andMOVE FORWARD :n
) variants move over exactly :n rows forwards from and including the row after the current position when this many rows are available and otherwise over just as many as it can analogously to howMOVE FORWARD ALL
behaves. -
The
MOVE BACKWARD :n
variant moves over exactly :n rows backwards from and including the row before the current position when this many rows are available and otherwise just as many as it can analogously to howMOVE BACKWARD ALL
behaves. -
The
MOVE ABSOLUTE :n
variant moves to the single row at exactly the indicated absolute position. TheMOVE RELATIVE :n
variant moves to the single row at exactly the indicated relative position (:n can be negative) to the current row. For bothMOVE ABSOLUTE :n
andMOVE RELATIVE :n
, the requested row might lie before the first row or after the last row. The outcome here is the same as it is when executing otherMOVE
variants that cause the current position to fall outside the range from the first through the last row in the cursor's result set. Notice that :n can be negative for both theABSOLUTE
and theRELATIVE
variants. -
Each of the
MOVE FIRST
andMOVE LAST
variants moves, respectively, to the first row or the last row. The meanings are therefore insensitive to the current cursor position, and each can be repeated time and again and will always have the same effect.
Notice that the three variants ,MOVE FORWARD 0
, MOVE BACKWARD 0
, and MOVE RELATIVE 0
, all mean the same as each other.
name
A cursor is identified only by an unqualified name and is visible only in the session that declares it. This determines the uniqueness scope for its name. (The name of a cursor is like that of a prepared statement in this respect.)
Simple example
bla
This is the result...
bla