FirebirdSQL logo

FETCH Examples

  1. Using the FETCH statement:

    CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
      RETURNS (RNAME CHAR(63))
    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
  2. Using the FETCH statement with nested cursors:

    EXECUTE BLOCK
      RETURNS (SCRIPT BLOB SUB_TYPE TEXT)
    AS
      DECLARE VARIABLE FIELDS VARCHAR (8191);
      DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME;
      DECLARE VARIABLE RELATION RDB$RELATION_NAME;
      DECLARE VARIABLE SRC TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
      -- Named cursor declaration
      DECLARE VARIABLE CUR_R CURSOR FOR (
        SELECT
          RDB$RELATION_NAME,
          RDB$VIEW_SOURCE
        FROM RDB$RELATIONS
        WHERE RDB$VIEW_SOURCE IS NOT NULL);
      -- Declaring a named cursor in which
      -- a local variable is used
      DECLARE CUR_F CURSOR FOR (
        SELECT RDB$FIELD_NAME
        FROM RDB$RELATION_FIELDS
        WHERE
        -- the variable must be declared earlier
          RDB$RELATION_NAME =: RELATION);
    BEGIN
      OPEN CUR_R;
      WHILE (1 = 1) DO
      BEGIN
        FETCH CUR_R INTO RELATION, SRC;
        IF (ROW_COUNT = 0) THEN
          LEAVE;
        FIELDS = NULL;
        -- Cursor CUR_F will use the value
        -- the RELATION variable initialized above
        OPEN CUR_F;
        WHILE (1 = 1) DO
        BEGIN
          FETCH CUR_F INTO FIELD_NAME;
          IF (ROW_COUNT = 0) THEN
            LEAVE;
          IF (FIELDS IS NULL) THEN
            FIELDS = TRIM (FIELD_NAME);
          ELSE
            FIELDS = FIELDS || ',' || TRIM(FIELD_NAME);
        END
        CLOSE CUR_F;
        SCRIPT = 'CREATE VIEW' || RELATION;
        IF (FIELDS IS NOT NULL) THEN
          SCRIPT = SCRIPT || '(' || FIELDS || ')' ;
        SCRIPT = SCRIPT || 'AS' || ASCII_CHAR (13);
        SCRIPT = SCRIPT || SRC;
        SUSPEND;
      END
      CLOSE CUR_R;
    EN
  3. An example of using the FETCH statement with a scrollable cursor

EXECUTE BLOCK
  RETURNS (N INT, RNAME CHAR (63))
AS
  DECLARE C SCROLL CURSOR FOR (
    SELECT
      ROW_NUMBER() OVER (ORDER BY RDB$RELATION_NAME) AS N,
      RDB$RELATION_NAME
    FROM RDB$RELATIONS
    ORDER BY RDB$RELATION_NAME);
BEGIN
  OPEN C;
  -- move to the first record (N = 1)
  FETCH FIRST FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move 1 record forward (N = 2)
  FETCH NEXT FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move to the fifth record (N = 5)
  FETCH ABSOLUTE 5 FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move 1 record backward (N = 4)
  FETCH PRIOR FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move 3 records forward (N = 7)
  FETCH RELATIVE 3 FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move back 5 records (N = 2)
  FETCH RELATIVE -5 FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move to the first record (N = 1)
  FETCH FIRST FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- move to the last entry
  FETCH LAST FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  CLOSE C;
END

Management Statements in PSQL

Management statement are allowed in PSQL modules (triggers, procedures, functions and PSQL blocks), which is especially helpful for applications that need management statements to be executed at the start of a session, specifically in ON CONNECT triggers.

The management statements permitted in PSQL are: