Product Documentation

Appearance

All platforms

FETCH ​

Fetch rows from a cursor.

FETCH [ direction [ FROM | IN ] ] name

where direction is any one of the following options (and name is an open cursor):

  • NEXT | FIRST | LAST
  • FORWARD [ count | ALL ]
  • RELATIVE count
  • count
  • ALL

If no direction is specified, the FETCH command fetches the next row.

See also MOVE cursor, which changes the position of a cursor without returning any rows. In general, the direction options work the same for both commands.

FETCH commands rely on the current position associated with the cursor. When you declare a cursor, it is positioned before the first row. After some rows are fetched, the cursor is positioned on the last retrieved row, and the next fetch proceeds from that position. If FETCH reaches the end of the available rows, the cursor is positioned after the last row.

NEXT, FIRST, LAST ​

NEXT, FIRST, and LAST fetch a single row after moving the cursor appropriately. If there is no such row, FETCH returns an empty result, and the cursor is positioned before the first row or after the last row (as appropriate).

FORWARD ​

Forward fetching and movement is the default behavior. FETCH and MOVE commands without a named direction default to FORWARD moves and fetches. Backward movement is in general not supported.

FORWARD returns the requested number of rows, moving forward, leaving the cursor positioned on the last-returned row (or after all rows if count exceeds the number of rows available). FORWARD without a count is equivalent to NEXT.

MOVE FORWARD 0 and FETCH FORWARD 0 behave as follows:

  • MOVE FORWARD 0: Returns MOVE 0 when specified after the cursor is declared and before any other moves or fetches. Returns MOVE 1 when specified after any other moves or fetches.
  • FETCH FORWARD 0: Returns no rows when specified after the cursor is declared and before any other moves or fetches. Returns an error (Cursor can only scan forward) when specified after other moves or fetches.

RELATIVE count ​

RELATIVE count, where count is positive, behaves like FETCH count. RELATIVE count, where count is negative, returns an error.

RELATIVE 0 and FORWARD 0 fetch the current row without moving the cursor. In other words, they fetch the most recently fetched row again. No row is returned if the cursor is positioned before the first row or after the last row.

MOVE RELATIVE 0 and FETCH RELATIVE 0 behave as follows:

  • MOVE RELATIVE 0: Returns MOVE 0 when specified after the cursor is declared and before any other moves or fetches. Returns MOVE 1 when specified after any other moves or fetches.
  • FETCH RELATIVE 0: Returns no rows when specified after the cursor is declared and before any other moves or fetches. Returns an error (Cursor can only scan forward) when specified after other moves or fetches.

count ​

If count is specified by itself, it must be a positive number or 0. See also RELATIVE count.

ALL ​

If ALL is specified by itself, it fetches all remaining rows from the current position of the cursor. FORWARD ALL and ALL are equivalent.

Minimal Support for ABSOLUTE and BACKWARD ​

In general, ABSOLUTE is not supported for moves and fetches. However, ABSOLUTE 1 and ABSOLUTE -1 are allowed when they are used immediately after a cursor is declared. In this case, ABSOLUTE 1 is equivalent to FIRST, and ABSOLUTE -1 is equivalent to LAST.

In general, BACKWARD and PRIOR are not supported for moves and fetches. Only forward fetches and movement are allowed. However, BACKWARD count, where count is a negative value, is allowed and behaves like FETCH count.

Examples ​

For example, within the following transaction, the goals cursor is declared, then rows are fetched forward 10 at a time until no rows are left to return:

premdb=# begin;
BEGIN
premdb=# declare goals cursor for 
select t1.season_name, t1.winners, homegoals+awaygoals as total
from
(select season_name, winners, sum(substr(ftscore,1,1)::int) homegoals
from season, match
where season.seasonid=match.seasonid
group by season_name, winners) t1,
(select season_name, winners, sum(substr(ftscore,3,1)::int) awaygoals
from season, match
where season.seasonid=match.seasonid
group by season_name, winners) t2
where t1.season_name=t2.season_name
order by 1,2;
DECLARE CURSOR
premdb=# fetch forward 10 from goals;
 season_name |      winners      | total 
-------------+-------------------+-------
 1992-1993   | Manchester United |  1222
 1993-1994   | Manchester United |  1195
 1994-1995   | Blackburn Rovers  |  1195
 1995-1996   | Manchester United |   988
 1996-1997   | Manchester United |   970
 1997-1998   | Arsenal           |  1019
 1998-1999   | Manchester United |   959
 1999-2000   | Manchester United |  1060
 2000-2001   | Manchester United |   992
 2001-2002   | Arsenal           |  1001
(10 rows)

premdb=# fetch forward 10 from goals;
 season_name |      winners      | total 
-------------+-------------------+-------
 2002-2003   | Manchester United |  1000
 2003-2004   | Arsenal           |  1012
 2004-2005   | Chelsea           |   975
 2005-2006   | Chelsea           |   944
 2006-2007   | Manchester United |   931
 2007-2008   | Manchester United |  1002
 2008-2009   | Manchester United |   942
 2009-2010   | Chelsea           |  1053
 2010-2011   | Manchester United |  1063
 2011-2012   | Manchester City   |  1066
(10 rows)

premdb=# fetch forward 10 from goals;
 season_name |      winners      | total 
-------------+-------------------+-------
 2012-2013   | Manchester United |  1063
 2013-2014   | Manchester City   |  1052
(2 rows)

premdb=# fetch forward 10 from goals;
 season_name | winners | total 
-------------+---------+-------
(0 rows)

premdb=# end;
COMMIT

In this example, FETCH LAST returns an error because the cursor has already been moved beyond the last row. Backward fetches are not supported, so going back to return the last row in the table is not possible.

premdb=# declare allmatches cursor for select * from match order by 1,2;
DECLARE CURSOR
premdb=# fetch first in allmatches;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
       1 | 1992-08-01 00:00:00 |   14 |   52 | 0-1     | -  
(1 row)

premdb=# move forward 8600 in allmatches;
MOVE 8600
premdb=# fetch last in allmatches;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
      22 | 2014-05-11 00:00:00 |   39 |   89 | 1-3     | 0-2
(1 row)

premdb=# fetch last in allmatches;
ERROR:  Cursor can only scan forward

The following FETCH commands demonstrate several different ways to fetch the next row:

premdb=# begin; 
BEGIN
premdb=# declare match cursor for select * from match order by 1,2,3,4;
DECLARE CURSOR
premdb=# fetch from match;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
       1 | 1992-08-01 00:00:00 |    2 |   52 | 0-1     | -  
(1 row)

premdb=# fetch in match;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
       1 | 1992-08-01 00:00:00 |    2 |   55 | 0-1     | -  
(1 row)

premdb=# fetch match;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
       1 | 1992-08-01 00:00:00 |    2 |   63 | 2-1     | -  
(1 row)

premdb=# fetch 1 match;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
       1 | 1992-08-01 00:00:00 |    2 |   64 | 3-0     | -  
(1 row)

In this example, FETCH FORWARD 0 returns no rows when run immediately after the cursor is declared, but returns an expected error when rows have been fetched or the cursor position has moved forward:

premdb=# declare match cursor for select * from match order by 1,2;
DECLARE CURSOR
premdb=# fetch forward 0 match;
 seasonid | matchday | htid | atid | ftscore | htscore 
----------+----------+------+------+---------+---------
(0 rows)

premdb=# fetch forward 0 match;
 seasonid | matchday | htid | atid | ftscore | htscore 
----------+----------+------+------+---------+---------
(0 rows)

premdb=# fetch 1 match;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
       1 | 1992-08-01 00:00:00 |   14 |   52 | 0-1     | -  
(1 row)

premdb=# fetch forward 0 match;
ERROR:  Cursor can only scan forward