Procedure in Redshift Return "SELECT query has no destination for result data" Error

Question

I keep getting the "SELECT query has no destination for result data" error upon calling this test procedure. What am I doing wrong? I did try adding the RETURN() command prior to SELECT statement but that didn't work either.

CREATE OR REPLACE PROCEDURE SchemaName.SP_Testing_Creating_Procedure (OUT ColumnName VARCHAR(9)) 
AS $$  
BEGIN   
 SELECT TOP 10 ColumnName FROM SchemaName.TableName where ColumnName is not null;     
END;
$$
LANGUAGE plpgsql; 

CALL SchemaName.SP_Testing_Creating_Procedure();

Your procedure is running a SELECT command. What do you want to do with the results of that query? — John Rotenstein

Pavel Slepiankou Pavel Slepiankou · Accepted Answer · 2021-08-18T10:02:20

As John mentioned you need to put the result into OUT column, examples of using IN, OUT and INOUT parameters you can find here

But if you need to return a few rows as a result, you have to use refcursor as it's described here

CREATE OR REPLACE PROCEDURE SchemaName.SP_Testing_Creating_Procedure (INOUT result refcursor)
AS $$
BEGIN
    OPEN result FOR
    SELECT TOP 10 ColumnName
    FROM SchemaName.TableName
    WHERE ColumnName IS NOT null;
END;
$$
LANGUAGE plpgsql;

Then you can call the stored procedure in a transaction

BEGIN;
CALL logs.SP_Testing_Creating_Procedure('mycursor');
FETCH ALL FROM mycursor;
COMMIT;

another option is temp table which is also described in the above doc

Procedure in Redshift Return "SELECT query has no destination for result data" Error

2 Answers