FETCH(l)                                                 FETCH(l)

NAME
       FETCH - Gets rows using a cursor

SYNOPSIS
       FETCH [ selector ] [ count ] { IN | FROM } cursor
       FETCH [ RELATIVE ] [ { [ # | ALL | NEXT | PRIOR ] } ] FROM ] cursor

   INPUTS
       selector
              selector defines the fetch direction. It can be one
              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 specify how many  rows
                     to  fetch.   Note that a negative integer is
                     equivalent to changing the sense of  FORWARD
                     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
              cursors.

       ERROR: FETCH/RELATIVE at current position is not supported
              SQL92 allows one to repetatively 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, the Postgres assumes that the
              user intended SQL92 behavior and returns this error
              message.

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.

       Once  all  rows  are  fetched,  every  other  fetch access
       returns no rows.

       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(l)  to  change cursor position.  declare(l) will
       define a cursor.  Refer to begin(l), commit(l), and  roll-
       back(l) for further information about transactions.

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
              are relative numbers so this capability is not sup-
              ported.

       :variable
              Target host variable(s).

SQL - Language Statements 15 August 1999                        1