Skip to main content

FETCH

Retrieves rows from a query by using a cursor.

Syntax​

FETCH [ <forward_direction> { FROM | IN } ] <cursor_name>
where <forward_direction> can be empty or one of:
    NEXT
    FIRST
    ABSOLUTE <count>
    RELATIVE <count>
    <count>
    ALL
    FORWARD
    FORWARD <count>
    FORWARD ALL

Description​

To execute FETCH, you must use an existing cursor. The cursor cannot be a parallel retrieve cursor.

Relyt does not support scrollable cursors. Therefore, FETCH can only be used to move the cursor forward.

A cursor has a position used by FETCH to fetch results. The position can be before the first row, on any row, or after the last row of the result set. When a cursor is created, the position is before the first row. Then the position moves to the most recently fetched row each time after a fetch is complete. After FETCH runs out of available rows, the cursor is left positioned after the last row. FETCH ALL leaves the cursor positioned after the last row.

The NEXT, FIRST, ABSOLUTE and RELATIVE FETCH forms fetch a single row and move the cursor accordingly. An empty result will be returned if no rows are found.

FORWARD FETCH retrieves the given number of rows in a forward direction and moves the cursor to the last-returned row. The cursor cannot be moved backward, because Relyt does not support scrollable cursors.

RELATIVE 0 and FORWARD 0 fetch the current row without moving the cursor. Successful execution of the FETCH statement returns a command tag containing the fetched row's count.

Parameters​

  • <forward_direction>

    The fetch direction and the number of rows to fetch. Currently, only forward fetches are supported and the values can be NEXT, FIRST, ABSOLUTE <count>, RELATIVE <count>, <count>, ALL, FORWARD, FORWARD <count>, and FORWARD ALL.

    • NEXT: specifies to retrieve the next row. If forward_direction is not specified, NEXT is used by default.
    • FIRST: specifies to retrieve the first row of the query. This direction can be used only for the first FETCH that uses the cursor. FIRST has the same effect as ABSOLUTE 0.
    • ABSOLUTE: specifies to retrieve the given row of the query. The value of count must be larger than the current position of the cursor to keep the cursor position moving forward.
    • RELATIVE: specifies to retrieve the row that is count rows ahead of the current cursor position. The value of count must be larger than the current position to keep the cursor position moving forward or 0 to retrieve the current row again.
    • <count>: specifies to retrieve the next count number of rows. It has the same effect as FORWARD <count>.
    • ALL: specifies to retrieve all remaining rows. It has the same effect as FORWARD ALL.
    • FORWARD: specifies to retrieve the next row. It has the same effect as NEXT.
    • FORWARD <count>: specifies to retrieve the next count number of rows. If count is set to 0, the current row will be retrieved again.
    • FORWARD ALL: specifies to retrieve all remaining rows.

  • <cursor_name>

    The name of the cursor.

Outputs​

If a FETCH is successful, the command tag returned is in the following form:

FETCH <count>

<count> specifies the number of rows fetched.

Examples​

  1. Start the transaction.

    BEGIN;

  2. Set up a cursor:

    DECLARE mycursor CURSOR FOR SELECT * FROM films;

  3. Fetch the first 5 rows in the cursor mycursor:

    FETCH FORWARD 5 FROM mycursor;
    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

  4. Close the cursor and end the transaction:

    CLOSE mycursor;
    COMMIT;

  5. Change the kind column of the table films in the row at the c_films cursor's current position:

    UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;

SQL standard compatibility​

FETCH in Relyt allows you to use cursors interactively, while FETCH in the SQL standard can be used only in embedded SQL and modules.

FETCH in Relyt is fully compatible with the SQL standard. Besides, FETCH in Relyt, as we have described above, returns the data in the same way as a SELECT statement.

In addition, the SQL standard only allows FROM to be placed before the cursor name. Relyt also allows you to use IN.