Redshift:从字符串执行动态查询 [英] Redshift: Executing a dynamic query from a string

问题描述

我想执行一个动态SQL查询，该查询存储在Amazon Redshift的字符串字段中.

I would like to execute a dynamic SQL query stored in a string field on Amazon Redshift.

我的背景主要是T-SQL关系数据库.我曾经动态地构建SQL语句，将它们存储到变量中并执行它们.我知道Redshift可以准备和执行语句，但是我想知道是否可以执行存储在字符串字段中的查询.

My background is mostly T-SQL relational databases. I used to build SQL statements dynamically, store them into variables and them execute them. I know Redshift can prepare and execute statements, but I wonder if it is possible to execute a query stored in a string field.

我有一段代码可以使用pg_ *系统表在多个表上使用统计信息动态构建以下代码.每个列/表名都是动态计算的.这是查询输出的示例:

I have a piece of code that dynamically builds the code below with stats on several tables using pg_* system tables. Every column/table name is dynamically calculated. Here's an example of the query output:

SELECT h_article_id AS key, 'transport_parameters_weight_in_grams' AS col_name, COUNT(DISTINCT transport_parameters_weight_in_grams) AS count_value FROM dv.s_products GROUP BY h_article_id UNION ALL
SELECT h_article_id AS key, 'transport_parameters_width_in_mm' AS col_name, COUNT(DISTINCT transport_parameters_width_in_mm) AS count_value FROM dv.s_products GROUP BY h_article_id UNION ALL
SELECT h_article_id AS key, 'label_owner_info_communication_address' AS col_name, COUNT(DISTINCT label_owner_info_communication_address) AS count_value FROM dv.s_products GROUP BY h_article_id

我想在另一个查询中输入此动态代码，因此我可以进行一些统计，如下所示:

I would like to input this dynamic piece of code within another query, so I can make some statistics, like so:

SELECT col_name, AVG(count_value*1.00) AS avg_count
FROM (
  'QUERY ABOVE'
) A
GROUP BY col_name;

这将输出如下内容:

col_name                                avg_count
transport_parameters_weight_in_grams    1.00
transport_parameters_width_in_mm        1.00
label_owner_info_communication_address  0.60

我这样做的自然方法是将所有内容存储为字符串并存储在变量中并执行它.但是，恐怕Redshift不支持此功能.

The natural way for me to do this would be to store everything as a string in a variable and execute it. But I'm afraid Redshift does not support this.

是否有其他方法可以真正构建动态SQL代码?

Is there an alternative way to really build dynamic SQL code?

推荐答案

现在，我们已经添加了对存储过程的支持，这是可能的. >"Amazon Redshift中存储过程概述"

This is possible now that we have added support for Stored Procedures. "Overview of Stored Procedures in Amazon Redshift"

例如，此存储过程对一个表中的行进行计数，并将表名和行数插入另一个表中.这两个表名均作为输入提供.

For example, this stored procedure counts the rows in a table and inserts the table name and row count into another table. Both table names are provided as input.

CREATE PROCEDURE get_tbl_count(IN source_tbl VARCHAR, IN count_tbl VARCHAR) AS $$
BEGIN
EXECUTE 'INSERT INTO ' || quote_ident(count_tbl) 
        || ' SELECT ''' || source_tbl ||''', COUNT(*) FROM ' 
        || quote_ident(source_tbl) || ';' 
RETURN;
END;
$$ LANGUAGE plpgsql;

在您的示例中，要执行的查询可以作为字符串传递.

In your example the query to executed could be passed in as a string.

这篇关于Redshift:从字符串执行动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！