Issue With Passing Column Name As A Parameter To "prepare" In Redshift

I am using REDSHIFT for the below question, Here is problem and I am looking for solution. I have 2 tables, one table contains the column combinations on which the second table sho

Solution 1:

This can now be done using Stored Procedures without the need for PREPARE. "Overview of Stored Procedures in Amazon Redshift"

It seems like you are trying to emulate GROUPING SETS or ROLLUP functionality. I have added a UNION ALL to the dynamic SQL to provide this type of output.

For this example stored procedure, both column names are provided as input and a REFCURSOR is declared as output.

CREATEPROCEDURE get_fruit_sum(IN column_1 VARCHAR, IN column_2 VARCHAR, result_set INOUT REFCURSOR) AS $$
BEGINOPEN result_set FOREXECUTE'SELECT '|| quote_ident(column_1) ||' , '|| quote_ident(column_2) 
        ||' , SUM(fb.user_Count) as user_count '||'FROM dv_product.fruit_basket fb GROUP BY 1,2'||'UNION ALL '||'SELECT '|| quote_ident(column_1) ||' , ''ALL'''||' , SUM(fb.user_Count) as user_count '||'FROM dv_product.fruit_basket fb GROUP BY 1;'RETURN;
END;
$$ LANGUAGE plpgsql;

You specify the columns and the output REFCURSOR when calling the procedure. The column names could be retrieved from a table by another stored procedure if needed. Then fetch the output from the REFCURSOR.

BEGIN; 
CALL get_fruit_sum ( 'Banana','Orange','result_set' ); 
FETCHALLFROM result_set; 
END;