Return SYS_REFCURSOR From Procedure - Oracle to PostgreSQL Migration

An Oracle stored procedure can return a cursor to the caller, for example:

Oracle:

  -- Get list of employees for the specified department
  CREATE OR REPLACE PROCEDURE getEmployeesByDept (
     p_deptno IN emp.deptno%TYPE, 
     p_recordset OUT SYS_REFCURSOR
  ) AS 
  BEGIN 
    OPEN p_recordset FOR
      SELECT empno, ename
      FROM  emp
      WHERE deptno = p_deptno
      ORDER BY ename;
  END getEmployeesByDept;
  /

Then you can call the procedure and fetch records from the returned cursor as follows:

Oracle:

  SET SERVEROUTPUT ON
 
  DECLARE
    cur SYS_REFCURSOR;
    empno emp.empno%TYPE;
    ename emp.ename%TYPE;
  BEGIN
    -- Get employees list
    getEmployeesByDept(p_deptno => 30, p_recordset => cur);
 
    -- Output the records  
    LOOP 
      FETCH cur INTO empno, ename;
      EXIT WHEN cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(empno || ', ' || ename);
    END LOOP;
    CLOSE cur;
  END;
  /

Sample output:

PostgreSQL also supports REFCURSOR but it is an IN parameter containing the cursor name, and the actual REFCURSOR value is returned using the RETURN statement:

PostgreSQL:

  -- Get list of employees for the specified department
  CREATE OR REPLACE FUNCTION getEmployeesByDept (
     p_deptno IN emp.deptno%TYPE, 
     p_recordset REFCURSOR
  ) RETURNS REFCURSOR AS $$ 
  BEGIN 
    OPEN p_recordset FOR
      SELECT empno, ename
      FROM  emp
      WHERE deptno = p_deptno
      ORDER BY ename;
 
      RETURN p_recordset;
  END;
  $$ LANGUAGE plpgsql;

Note that the PROCEDURE was converted to FUNCTION, RETURNS REFCURSOR clause and RETURN statements were added. Now you can call this function from a PL/pgSQL block as follows:

PostgreSQL:

  DO $$
  DECLARE
    cur REFCURSOR = 'cur';
    empno emp.empno%TYPE;
    ename emp.ename%TYPE;
  BEGIN
    -- Get employees list
    PERFORM getEmployeesByDept(p_deptno := 30, p_recordset := 'cur');
 
    -- Output the records  
    LOOP 
      FETCH cur INTO empno, ename;
      EXIT WHEN NOT FOUND;
      RAISE NOTICE '%',empno || ', ' || ename;
    END LOOP;
    CLOSE cur;
  END;
  $$;

Sample output:

Oracle allows you to return multiple SYS_REFCURSORs from a stored procedures, for example:

Oracle:

-- Get list of employees for the specified department
  CREATE OR REPLACE PROCEDURE getEmployeesByDept2 (
     p_deptno IN emp.deptno%TYPE, 
     p_recordset OUT SYS_REFCURSOR,
     p_recordset2 OUT SYS_REFCURSOR
  ) AS 
  BEGIN 
    OPEN p_recordset FOR
      SELECT empno, ename
      FROM  emp
      WHERE deptno = p_deptno
      ORDER BY ename;
 
    OPEN p_recordset2 FOR
      SELECT empno, ename
      FROM  emp
      WHERE deptno = p_deptno;
 
  END getEmployeesByDept2;
  /

PostgreSQL also allows you to return multiple REFCURSORs from a stored procedure, for example:

PostgreSQL:

  CREATE OR REPLACE FUNCTION getEmployeesByDept3 (
     p_deptno IN emp.deptno%TYPE, 
     p_recordset REFCURSOR,
     p_recordset2 REFCURSOR
  ) RETURNS SETOF REFCURSOR AS $$ 
  BEGIN 
    OPEN p_recordset FOR
      SELECT empno, ename
      FROM  emp
      WHERE deptno = p_deptno
      ORDER BY ename;
    RETURN NEXT p_recordset;
 
    OPEN p_recordset2 FOR
      SELECT empno, ename
      FROM  emp
      WHERE deptno = p_deptno;
    RETURN NEXT p_recordset2;  
 
  END;
  $$ LANGUAGE plpgsql;

Note that you have to specify SETOF in the RETURNS clause, and use RETURN NEXT to return every cursor.

For more information, see Oracle to PostgreSQL Migration.