Redshift: Performing a dynamic query from a string

Question

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

My background is mostly T-SQL relational databases. I used dynamic SQL statements, saved them in variables, and executed them. I know that Redshift can prepare and execute instructions, but I wonder if it is possible to execute a query stored in a row field.

I have a piece of code that dynamically creates the code below with statistics in multiple tables using the pg_ * system tables. Each column / table name is dynamically computed. Here is an example of 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 inject this dynamic code snippet into another query, so I can do some statistics, for example:

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

That would mean something like:

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 .

SQL?

Answer 1

. SQL Redshift.

SQL , MS SQL Server.

Python Redshift, Python SQL.

"PREPARE" "EXECUTE" SQL, , execute. , - .... "" .

, SQL SQL .

, - AWS Data Pipeline.

Answer 2

Postgre Redshift, .

, .

date = dt.date(2018, 10, 30)

query = ''' select * from table where date >= ''' + str(my_date) + ''' order by date '''

.

, (%), .

:

query = ''' select * from table where date >= ''' + ''' '%s' ''' % my_date + ''' order by date '''

, , , . , !

.

Answer 3

, . " Amazon Redshift"

, SProc . .

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;

.