Return Result Set from Stored Procedure - SQL Server to PostgreSQL Migration

SQL Server allows a stored procedure to return a result set to the caller by executing a standalone SELECT statement:

SQL Server:

  -- Return a result set from stored procedure
  CREATE PROCEDURE sp_getCities
  AS
    SELECT 'Boston' as city
    UNION ALL 
    SELECT 'Barcelona' as city
    UNION ALL 
    SELECT 'Munich' as city
  GO

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

SQL Server:

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

Result:

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

PostgreSQL:

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

Then you can call it as follows:

PostgreSQL:

  -- Start a transaction
  BEGIN;
 
  -- Invoke the function that returns a cursor
  SELECT sp_getCities('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
  SELECT sp_getCities('cur');
 
  FETCH ALL IN cur;
  # ERROR:  cursor "cur" does not exist

For more information, see SQL Server to PostgreSQL Migration.