EXECUTE - Dynamic Result Set - SQL Server to PostgreSQL Migration

In SQL Server stored procedure you can retun a dynamic result set to the caller as follwos:

SQL Server:

  -- Return a dynamic result set from stored procedure
  CREATE PROCEDURE sp_getColors
  AS
    EXECUTE('
    SELECT ''Red'' AS color
    UNION ALL 
    SELECT ''Blue'' AS color
    UNION ALL 
    SELECT ''Green'' AS color')
  GO

Then if you call this procedure, it will return multiple rows:

SQL Server:

  -- Invoke the procedure that returns a result set
  EXEC sp_getColors

Result:

In PostgreSQL you can return a result set from a procedure or function by defining a REFCURSOR parameter and using OPEN FOR EXECUTE SELECT statement as follows:

PostgreSQL:

  CREATE OR REPLACE PROCEDURE sp_getColors(IN OUT cur REFCURSOR)
  AS $$
  BEGIN
    OPEN cur FOR EXECUTE
    'SELECT ''Red'' AS color
     UNION ALL 
     SELECT ''Blue'' AS color
     UNION ALL 
     SELECT ''Green'' AS color';
  END;
  $$ LANGUAGE plpgsql;

Then you can call it as follows:

PostgreSQL:

  -- Start a transaction
  BEGIN;
 
  -- Invoke the procedure that returns a cursor
  CALL sp_getColors('cur');
 
  -- Read rows from the cursor
  FETCH ALL IN cur;

Result:

Note that you have to start a transaction to see the output data using FETCH ALL statement, otherwise the cursor will be automatically closed after the function/procedure call:

PostgreSQL:

  -- Without start a transaction
  CALL sp_getColors('cur');
 
  FETCH ALL IN cur;
  # ERROR:  cursor "cur" does not exist

For more information, see SQL Server to PostgreSQL Migration.