英文:
Is it possible to run a query in Redshift that uses a variable without EXECUTE statement?
问题
I'm just curious. I stumbled upon this code and wanted to get rid of EXECUTE. It worked for the DROP statement, but I couldn't figure out how to do it for the CREATE because it's using data saved to a variable.
BEGIN
SELECT
INTO row '''' + LISTAGG(DISTINCT key, ''', ''') + '''' as keys
FROM
some_table;
EXECUTE 'DROP TABLE IF EXISTS some_table_pivot;';
EXECUTE 'CREATE TABLE DROP TABLE IF EXISTS some_table_pivot;
AS SELECT * FROM (SELECT account_id, key, value FROM DROP TABLE IF EXISTS some_table) PIVOT (MAX(lower(value)) FOR "key" in (' || row.keys || '));';
Does anyone know if it's possible?
英文:
I'm just curious. I stumbled upon this code and wanted to get rid of EXECUTE. It worked for the DROP statement, but i couldn't figure out how do it for the CREATE because it's using data saved to a variable.
BEGIN
SELECT
INTO row '''' + LISTAGG(DISTINCT key, ''', ''') + '''' as keys
FROM
some_table;
EXECUTE 'DROP TABLE IF EXISTS some_table_pivot;';
EXECUTE 'CREATE TABLE DROP TABLE IF EXISTS some_table_pivot;
AS SELECT * FROM (SELECT account_id, key, value FROM DROP TABLE IF EXISTS some_table) PIVOT (MAX(lower(value)) FOR "key" in (' || row.keys || '));';
does anyone know if it's possible?
答案1
得分: 1
这似乎是存储过程内部的代码，对吗？在存储过程内部运行动态SQL的唯一方法是使用EXECUTE。
您可以使用一些外部支持代码将创建的SQL发送到Redshift。这可以是Lambda、shell脚本或任何可以通过JDBC/ODBC或API连接的工具。所以几乎可以使用任何工具。
英文:
This looks to be the code inside a stored procedure, no? An EXECUTE inside a stored procedure is the only way to run dynamic SQL internally to Redshift.
You could use some external support code to issue created SQL to Redshift. This could be a Lambda, shell scripts, or any tool that can connect via JDBC/ODBC or API. So just about anything.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论