FETCH - Gets rows using a cursor


SYNOPSIS

       FETCH [ direction ] [ count ] { IN | FROM } cursor
       FETCH [ FORWARD | BACKWARD | RELATIVE ] [ # | ALL | NEXT | PRIOR ] { IN | FROM } cursor

   INPUTS
       direction
              selector defines the fetch direction. It can be one
              of the following:

              FORWARD
                     fetch next row(s). This is  the  default  if
                     selector is omitted.

              BACKWARD
                     fetch previous row(s).

              RELATIVE
                     Noise word for SQL92 compatibility.

       count  count  determines how many rows to fetch. It can be
              one of the following:

              #      A signed integer  that  specifies  how  many
                     rows to fetch.  Note that a negative integer
                     is equivalent to changing the sense of  FOR­
                     WARD and BACKWARD.

              ALL    Retrieve all remaining rows.

              NEXT   Equivalent to specifying a count of 1.

              PRIOR  Equivalent to specifying a count of -1.

       cursor An open cursor's name.

   OUTPUTS
       FETCH  returns  the  results  of  the query defined by the
       specified cursor.  The following messages will be returned
       if the query fails:

       NOTICE: PerformPortalFetch: portal "cursor" not found
              If  cursor  is not previously declared.  The cursor
              must be declared within a transaction block.

       NOTICE: FETCH/ABSOLUTE not supported, using RELATIVE
              Postgres does not support absolute  positioning  of


       ERROR: FETCH/RELATIVE at current position is not supported
              SQL92 allows one to repetitively retrieve the  cur­
              sor at its "current position" using the syntax

              FETCH RELATIVE 0 FROM cursor.

              Postgres does not currently support this notion; in
              fact the value zero is reserved  to  indicate  that
              all  rows  should be retrieved and is equivalent to
              specifying the ALL keyword.  If the  RELATIVE  key­
              word  has been used, Postgres assumes that the user
              intended SQL92 behavior and returns this error mes­
              sage.


DESCRIPTION

       FETCH  allows a user to retrieve rows using a cursor.  The
       number of rows retrieved is specified by #.  If the number
       of  rows remaining in the cursor is less than #, then only
       those available are fetched.  Substituting the keyword ALL
       in  place of a number will cause all remaining rows in the
       cursor to be retrieved.  Instances may be fetched in  both
       FORWARD  and BACKWARD directions. The default direction is
       FORWARD.

              Tip: Negative numbers are allowed to  be  specified
              for  the row count. A negative number is equivalent
              to reversing the sense of the FORWARD and  BACKWARD
              keywords.  For  example,  FORWARD -1 is the same as
              BACKWARD 1.

   NOTES
       Note that the FORWARD and BACKWARD keywords  are  Postgres
       extensions.  The SQL92 syntax is also supported, specified
       in the second form of the command. See below  for  details
       on compatibility issues.

       Updating  data  in  a cursor is not supported by Postgres,
       because mapping cursor updates back to base tables is  not
       generally possible, as is also the case with VIEW updates.
       Consequently, users must issue explicit UPDATE commands to
       replace data.

       Cursors  may  only  be used inside of transactions because
       the data that they store spans multiple user queries.

       Use MOVE [move(l)] to  change  cursor  position.   DECLARE
       [declare(l)]   will  define  a  cursor.   Refer  to  BEGIN
       [begin(l)], COMMIT [commit(l)], and ROLLBACK [rollback(l)]



USAGE

       The following examples traverses a table using a cursor.

       -- Set up and use a cursor:

       BEGIN WORK;
       DECLARE liahona CURSOR FOR SELECT * FROM films;

       -- Fetch first 5 rows in the cursor liahona:
       FETCH FORWARD 5 IN liahona;

        code  |          title          | did | date_prod  |  kind    | len
       -------+-------------------------+-----+------------+----------+-------
        BL101 | The Third Man           | 101 | 1949-12-23 | Drama    | 01:44
        BL102 | The African Queen       | 101 | 1951-08-11 | Romantic | 01:43
        JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25
        P_301 | Vertigo                 | 103 | 1958-11-14 | Action   | 02:08
        P_302 | Becket                  | 103 | 1964-02-03 | Drama    | 02:28

       -- Fetch previous row:
       FETCH BACKWARD 1 IN liahona;

        code  | title   | did | date_prod  | kind   | len
       -------+---------+-----+------------+--------+-------
        P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08

       -- close the cursor and commit work:

       CLOSE liahona;
       COMMIT WORK;


COMPATIBILITY

   SQL92
              Note: The non-embedded use of cursors is a Postgres
              extension. The syntax and usage of cursors is being
              compared  against  the  embedded  form  of  cursors
              defined in SQL92.

       SQL92 allows absolute positioning of the cursor for FETCH,
       and allows placing the results into explicit variables:

       FETCH ABSOLUTE #
           FROM cursor
           INTO :variable [, ...]

       ABSOLUTE
              The  cursor  should  be positioned to the specified
              absolute row number. All row  numbers  in  Postgres

              ported.

       :variable
              Target host variable(s).


Man(1) output converted with man2html