7.6.14. FETCH

Used for

Fetching successive records from a data set retrieved by a cursor

Available in

PSQL

Syntax

FETCH cursorname INTO [:]varname [, [:]varname ...]

A FETCH statement fetches the first and successive rows from the result set of the cursor and assigns the column values to PSQL variables. The FETCH statement can be used only with a cursor declared with the DECLARE CURSOR statement.

The INTO clause gets data from the current row of the cursor and loads them into PSQL variables.

For checking whether all of the the data set rows have been fetched, the context variable ROW_COUNT returns the number of rows fetched by the statement. It is positive until all rows have been checked. A ROW_COUNT of 1 indicates that the next fetch will be the last.

Example

Using the FETCH statement:

SET TERM ^;
CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
RETURNS (
  RNAME CHAR(31)
)
AS
  DECLARE C CURSOR FOR (
    SELECT RDB$RELATION_NAME
    FROM RDB$RELATIONS);
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :RNAME;
    IF (ROW_COUNT = 0) THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^
SET TERM ;^

See also

DECLARE CURSOR, OPEN, CLOSE