Return Result Set - Sybase SQL Anywhere to PostgreSQL Migration

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

Sybase SQL Anywhere:

  -- Return a result set from stored procedure
  CREATE PROCEDURE sp_getColors()
  RESULT (
    id INT,
    name VARCHAR(30))
  BEGIN
    SELECT 1, 'White'
    UNION ALL 
    SELECT 2, 'Black'
    UNION ALL 
    SELECT 3, 'Green'
  END
  GO

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

Sybase SQL Anywhere:

  -- Invoke the procedure that returns a result set
  CALL sp_getCities()

Result:

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

PostgreSQL:

  CREATE OR REPLACE PROCEDURE sp_getColors(cur REFCURSOR)
  AS $$
  BEGIN
    OPEN cur FOR 
      SELECT 1::INT AS id, 'White'::VARCHAR(30) AS name
      UNION ALL 
      SELECT 2, 'Black'
      UNION ALL 
      SELECT 3, 'Green';
  END;
  $$ LANGUAGE plpgsql;

PostgreSQL does not support a RESULT clause to define the result set columns and types, but we can use the column aliases and casting operators in the query.

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;
 
  COMMIT;

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 a transaction
  CALL sp_getColors('cur');
 
  FETCH ALL IN cur;
  /* ERROR:  cursor "cur" does not exist */

For more information, see Sybase SQL Anywhere to PostgreSQL Migration.