通过将列名作为参数传递给"PREPARE"的问题得到解决.在Redshift [英] Issue with passing column name as a parameter to "PREPARE" in Redshift

问题描述

我正在使用REDSHIFT解决以下问题，这是问题，我正在寻找解决方案.

I am using REDSHIFT for the below question, Here is problem and I am looking for solution.

我有2个表，一个表包含列组合，第二个表应在该组合上对结果进行分组.

I have 2 tables, one table contains the column combinations on which the second table should group the results by.

表1包含列组合(这是从存储过程输出的):

Table 1 containing column combinations (This is output from a stored procedure):

COMBINATIONS

fruit_combinations 
  banana,'ALL'  
  banana, orange

表2包含fruit_baskets:

Table 2 containing fruit_baskets:

FRUIT_BASKET

BANANA ORANGE USER_COUNT 
  b1     o1      5
  b1     o2      10

结果集:

FRUIT_BASKET_AGG

BANANA   ORANGE    USER_COUNT 
  b1     'ALL'       15
  b1      o1         5
  b1      o2         10

在Redshift中尝试过的解决方案:

Tried solution in Redshift:

PREPARE executesql(VARCHAR(10),VARCHAR(10))
AS 
select $1,$2, sum(fb.user_Count) as user_count
from dv_product.fruit_basket fb
group by 1,2;

EXECUTE executesql("BANANA","ORANGE") ;
DEALLOCATE executesql;

Error: [Amazon](500310) Invalid operation: column "banana" does not exist;

问题:

1-有什么方法可以将列名作为参数传递给sql语句并执行sql?

1 - Is there a way I can pass column names as parameter to a sql statement and execute the sql?

2-在Redshift中获得上述结果集的其他替代方法是什么?

2 - What is other alternative to achieve the result set mentioned above in Redshift?

推荐答案

现在可以使用存储过程完成 而不需要PREPARE. >"Amazon Redshift中存储过程概述"

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

似乎您正在尝试模拟GROUPING SETS或ROLLUP功能.我在动态SQL中添加了UNION ALL来提供这种类型的输出.

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.

在此示例存储过程中，两个列名称均作为输入提供，而REFCURSOR被声明为输出.

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

CREATE PROCEDURE get_fruit_sum(IN column_1 VARCHAR, IN column_2 VARCHAR, result_set INOUT REFCURSOR) AS $$
BEGIN
OPEN result_set FOR
EXECUTE '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;

在调用过程时，可以指定列和输出REFCURSOR.如果需要，可以通过另一个存储过程从表中检索列名.然后从REFCURSOR获取输出.

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' ); 
FETCH ALL FROM result_set; 
END;

这篇关于通过将列名作为参数传递给"PREPARE"的问题得到解决.在Redshift的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！