DECLARE CURSOR WITH RETURN - IBM DB2 to PostgreSQL Migration

DB2 allows you to return one or more result sets from a stored procedure. You can declare a cursor specifying WITH RETURN clause that allows you to open the cursor and return its rows to the application, for example:

IBM DB2:

  --#SET TERMINATOR /
 
  CREATE PROCEDURE sp_selectDept(IN p_deptno CHARACTER(5))
  RESULT SETS 1 
  LANGUAGE SQL
  BEGIN
    DECLARE cur CURSOR WITH RETURN FOR 
      SELECT * FROM dept WHERE deptno = p_deptno;
    OPEN cur;
  END
  /

Then after you call the procedure you can fetch the results returned by the cursor:

IBM DB2:

  CALL sp_selectDept('A00')
  /
 
  -- DEPTNO DEPTNAME                                   MGRNO    ADMRDEPT    LOCATION
  -- --------- ------------------------------------    ----------   ------------    -----------
  -- A00       SPIFFY COMPUTER SERVICE DIV.   000010     A00              -

In PostgreSQL you have to specify that the function returns REFCURSOR and use the RETURN statement to return the rows of the open cursor to the application, the corresponding example:

PostgreSQL:

  CREATE OR REPLACE FUNCTION sp_selectDept(IN p_deptno CHARACTER(5)) 
    RETURNS REFCURSOR
  AS $$
  DECLARE cur CURSOR FOR 
    SELECT * FROM dept WHERE deptno = p_deptno;
  BEGIN
     OPEN cur;
     RETURN cur;
  END;
  $$ LANGUAGE plpgsql;

Then you can open a transaction, call the function and fetch the cursor as follows:

PostgreSQL:

  BEGIN;
 
  SELECT sp_selectDept('A00');
 
  -- sp_selectDept
  -------------------
  -- cur
  -- (1 row)
 
  FETCH ALL IN "cur";
 
  -- DEPTNO DEPTNAME                                   MGRNO    ADMRDEPT    LOCATION
  -- --------- ------------------------------------    ----------   ------------    -----------
  -- A00       SPIFFY COMPUTER SERVICE DIV.   000010     A00              -

In DB2 if you declare and open multiple WITH RETURN cursors you can return multiple result sets to the application, for example:

DB2:

  --#SET TERMINATOR /
 
  CREATE PROCEDURE sp_selectDept2(IN p_deptno CHARACTER(5))
  RESULT SETS 2 
  LANGUAGE SQL
  BEGIN
    DECLARE cur CURSOR WITH RETURN FOR 
      SELECT * FROM dept WHERE deptno = p_deptno;
    DECLARE cur2 CURSOR WITH RETURN FOR 
    SELECT * FROM dept;
  OPEN cur;
  OPEN cur2;
  END
  /

In PostgreSQL, you can specify that the function returns SETOF REFCURSOR, and use RETURN NEXT for every cursor, the corresponding example:

PostgreSQL:

  CREATE OR REPLACE FUNCTION sp_selectDept2(IN p_deptno CHARACTER(5)) 
    RETURNS SETOF REFCURSOR
  AS $$
  DECLARE 
    cur CURSOR FOR 
      SELECT * FROM dept WHERE deptno = p_deptno;
    cur2 CURSOR FOR 
      SELECT * FROM dept;
  BEGIN
    OPEN cur;
    OPEN cur2;
    RETURN NEXT cur;
    RETURN NEXT cur2;
  END;
  $$ LANGUAGE plpgsql;

Stored procedures are available since PostgreSQL 12 version and a stored procedure can also be used to return one or multiple result sets, so you do not need to convert DB2 stored procedure to a function in PostgreSQL:

DB2:

  --#SET TERMINATOR /
 
  CREATE PROCEDURE sp_getCities
  DYNAMIC RESULT SETS 1
  BEGIN
    DECLARE cur CURSOR WITH RETURN FOR 
      SELECT 'Malaga' AS city FROM sysibm.sysdummy1
      UNION ALL 
      SELECT 'Seville' AS city FROM sysibm.sysdummy1
      UNION ALL 
      SELECT 'Barcelona' AS city FROM sysibm.sysdummy1;
  OPEN cur;
  END
  /
 
  --#SET TERMINATOR ;

Calling this procedure:

DB2:

  CALL sp_getCities;
 
  # CITY
  # ---------
  # Malaga
  # Seville
  # Barcelona

In PostgreSQL you can use the following procedure:

PostgreSQL:

  CREATE OR REPLACE PROCEDURE sp_getCities(OUT cur REFCURSOR)
  AS $$
    BEGIN
      OPEN cur FOR 
        SELECT 'Boston' as city
        UNION ALL 
        SELECT 'Barcelona' as city
        UNION ALL 
        SELECT 'Munich' as city;
    END;
    $$ LANGUAGE plpgsql;

Note that a RECURSOR parameter is added and the cursor declaration is not required anymore. You can call this procedure as follows:

PostgreSQL:

  DO $$
  DECLARE
    cur REFCURSOR;
    name VARCHAR(30);
  BEGIN
    CALL sp_getCities(cur);
  LOOP 
      FETCH cur INTO name;
      EXIT WHEN NOT FOUND;
      RAISE NOTICE '%', name;
    END LOOP;
    CLOSE cur;
  END;
  $$;
 
  # Malaga
  # Seville
  # Barcelona

For more examples, please see IBM DB2 to PostgreSQL Migration Reference.