Redshift Stored Procedure Return Result Set

Redshift stored procedures are used to encapsulate business logic such as transformation, data validation, etc. You can also use the stored procedure to return the result set in your applications. Typically, stored procedure returns a unique value, it can also return result set in the form of cursor or temporary tables. In this article, we will check Redshift Stored Procedure Return Result set with an example.

Return records from the Redshift stored procedure is bit different compared to other postgreSQL databases such as Netezza.

Stored procedures in a Redshift database can return results in two ways:

Create Cursor and Return Cursor.
Create Temporary Table inside stored procedure and populate it.

Let us check these two methods in detail.

Create Cursor and Return Cursor

This method required you create cursor inside stored procedure. Because of performance issue, this method is recommended for only small data set. if you need to return large data set, than probably you should skip to second method. i.e. using temp table to return result set.

To return a cursor, create a procedure with an INOUT argument defined with a refcursor data type. When you call the procedure by providing cursor a name, then you can fetch the results from the cursor by name outside in you current session.

For example, consider below sample procedure to return result set.

CREATE OR REPLACE PROCEDURE sample_return_cursor (cursor_out INOUT refcursor)
AS $$
BEGIN
  OPEN cursor_out FOR SELECT * FROM test_table1;
END;
$$ LANGUAGE plpgsql;

Now you can call a procedure by passing a new cursor name as an argument and fetch record from that new cursor.

BEGIN;
CALL sample_return_cursor ('newcursor');
FETCH NEXT FROM newcursor;
CLOSE newcursor;

Here is the output:

dev=# CALL sample_return_cursor ('newcursor');
 cursor_out
------------
 newcursor
(1 row)

dev=# FETCH FORWARD 3 FROM newcursor;
 col1 | col2
------+------
    1 | AA
    2 | BB
    3 | CC
(3 rows)

dev=# CLOSE newcursor;
CLOSE CURSOR
dev=#

Create Temp Table inside stored procedure and Populate

The second method is to create a temp table inside stored procedure and populate it. This method is one of the easiest method and can work with large data sets.

To return results, you can create and return a handle to a temp table containing result rows. The client applications can supply a name as a parameter to the stored procedure. Inside the stored procedure, dynamic SQL can be used to create and populate the temp table.

For example, consider below sample procedure to return result set using temp table.

CREATE PROCEDURE sample_return_temp_table(tmp_table INOUT varchar(256)) as $$
DECLARE
  row record;
BEGIN
  EXECUTE 'drop table if exists ' || tmp_table;
  EXECUTE 'create temp table ' || tmp_table || ' as select * from test_table1';
END;
$$ LANGUAGE plpgsql;

Now you can call a procedure by passing the temp table name as a parameter and use records from that table for your subsequent calculations.

CALL sample_return_temp_table('temp_table');

 tmp_table
------------
 temp_table
(1 row)

dev=# SELECT * from temp_table;
 col1 | col2
------+------
    1 | AA
    3 | CC
    2 | BB
(3 rows)

Hope this helps 🙂