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;

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