Skip to content

MOVE cursor

Change the position of a cursor without returning any rows.

MOVE [ direction [ FROM | IN ] ] name

where direction is one of the options defined in the description of FETCH, and name is an open cursor.

For example, declare a cursor, move 1, then move 10. FETCH NEXT returns the 12th row in the table:

premdb=# begin;
BEGIN
premdb=# declare team cursor for select * from team order by teamid;
DECLARE CURSOR
premdb=# move team;
MOVE 1
premdb=# move forward 10 in team;
MOVE 10
premdb=# fetch next from team;
 teamid | htid | atid |       name        | nickname |  city  |  stadium   | capacity 
--------+------+------+-------------------+----------+--------+------------+----------
    12 |   13 |   62 | Charlton Athletic | Addicks  | London | The Valley |    27111
(1 row)

In this example, the second MOVE 10 command returns MOVE 6 because the end of the rows in the cursor has been reached:

premdb=# select count(*) from match;
 count 
-------
  8606
(1 row)
premdb=# begin;
BEGIN
premdb=# declare match cursor for select * from match order by 1,2,3,4;
DECLARE CURSOR
premdb=# move 8000 match;
MOVE 8000
premdb=# fetch 600 match;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
      21 | 2012-12-08 00:00:00 |   46 |   82 | 2-2     | 1-1
      21 | 2012-12-09 00:00:00 |   18 |   91 | 2-1     | 0-0
      21 | 2012-12-09 00:00:00 |   25 |   75 | 2-3     | 0-2
      21 | 2012-12-09 00:00:00 |   45 |   73 | 2-3     | 2-1
      21 | 2012-12-10 00:00:00 |   19 |   77 | 2-1     | 1-0
      21 | 2012-12-11 00:00:00 |   39 |   83 | 3-0     | 2-0
...
premdb=# move 10 match;
MOVE 6
premdb=# fetch next match;
 seasonid | matchday | htid | atid | ftscore | htscore 
----------+----------+------+------+---------+---------
(0 rows)

See also FETCH.

Parent topic:SQL Commands