FETCH SQL - Language Statements FETCH Gets rows using a cursor 1998-09-01 FETCH [ selector ] { [ # | ALL ] } { IN | FROM } cursor 1998-09-01 selector Selector define fetch direction and it can be one the following: FORWARD fetch next row(s), it is assumed by default if selector is omitted. BACKWARD fetch previous row(s). # An unsigned integer that specify how many rows to fetch. ALL Retrieve all remaining rows. cursor An open cursor's name. 1998-04-15 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. 1998-04-15 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. 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, similarly to VIEW updates. Consequently, users must issue explicit replace commands to update data. Cursors may only be used inside of transactions because the data that they store spans multiple user queries. 1998-04-15 Refer to MOVE statements to change cursor position. Refer to DECLARE statements to declare a cursor. Refer to BEGIN WORK, COMMIT WORK, ROLLBACK WORK statements for further information about transactions. --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; 1998-09-01 SQL92 specifies some additional capabilities for FETCH statement. FETCH [ [ selector ] FROM ] cursor INTO :variable [, ...] selector Defines the fetch direction with one of the following values: NEXT Fetch next row, it is assumed by default if selector is omitted. This is the only legal selector unless cursor is declared with the SCROLL option. PRIOR Fetch previous row. FIRST Fetch first row. LAST Fetch last row. ABSOLUTE # Refers to the #th row in the table associated with the cursor. RELATIVE # Refers to the #th row relative to the cursor position. A negative number is equivalent to reversing the sense of the FORWARD and BACKWARD keywords. cursor A cursor previously defined in the same transaction block using BEGIN and DECLARE. :variable Target host variable(s).