Returning a temporary table in Stored Procedure in Redshift

Question

Hello everyone! The problem that I'm having is that I need for a stored procedure in redshift to return a temporary table. Not in the way described here https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-result-set.html

Where you can see that the temp table is called after the stored procedure with "select * from myresult;"

What I need is to have the table returned when executing the CALL get_result_set() for example.

In my case I'm working with stored procedure that uses two date variables to create several temp tables, insert them values and at the end it joins every temp table created. And what I need is to have the table returned right after i execute the call command.

The reason why I need this kind of solution is because we need to create a report in power bi and we want to do it in the direct query section (advanced options for database connections).

MilindOke · Accepted Answer

The docs page provides two methods for using Stored Procedures. The first one returns the table data as a reference cursor. So the calling application needs to "fetch" from the cursor (which is like a table). The second method does not return the data, instead it just returns the name of a table. So the calling application needs to perform a "select" query to get the data.

The method #1 is actually returning the table's data for you to directly visualize upon.

I am not that familiar on PowerBI but hope this helps.

The method #1 is actually returning the table's data for you to directly visualize upon.

I am not that familiar on PowerBI but hope this helps.

Returning a temporary table in Stored Procedure in Redshift

Relevant content