如何在Redshift过程中写入动态创建的表 [英] how to write to dynamically created table in Redshift procedure

问题描述

我需要在Redshift中编写一个将写入表的过程，但是表名称来自输入字符串.然后，我声明一个将表名放在一起的变量.

I need to write a procedure in Redshift that will write to a table, but the table name comes from the input string. Then I declare a variable that puts together the table name.

CREATE OR REPLACE PROCEDURE my_schema.data_test(current "varchar")
    LANGUAGE plpgsql
AS $$                                                        
declare new_table varchar(50) = 'new_tab' || '_' || current;

BEGIN 
    select 'somestring' as colname into new_table;
    commit;
END;
$$

此代码运行，但不会创建新表，也不会出错.如果删除声明语句，则它起作用，创建一个名为"new_table"的表.它只是不使用声明的变量名.

This code runs but it doesn't create a new table, no errors. If I remove the declare statement then it works, creating a table called "new_table". It's just not using the declared variable name.

很难找到好的例子，因为Redshift是postgresql，并且所有的postgresql页面都说它仅具有函数，而没有过程.但是去年引入了Redshift程序，我看不到很多例子.

It's hard to find good examples because Redshift is postgresql and all the postgresql pages say that it only has functions, not procedures. But Redshift procedures were introduced last year and I don't see many examples.

推荐答案

好，当您声明变量"new_table"并执行SELECT ..INTO"new_table"时，该值将分配给变量"new_table"".如果使用OUT参数返回变量，您会看到.

Well, when you are declaring a variable "new_table", and performing a SELECT ..INTO "new_table", the value is getting assigned to the variable "new_table". You will see that if you return your variable using a OUT parameter.

当您删除声明时，它只是作为Redshift SQL的SELECT INTO语法工作并创建表.

And when you remove the declaration, it simply work as a SELECT INTO syntax of Redshift SQL and creates a table.

现在要解决:

使用CREATE TABLE AS ...语法创建表.

Create a table using the CREATE TABLE AS...syntax.

此外，您还需要传递声明的变量的值，因此请使用EXECUTE命令.

Also you need to pass the value of declared variable, so use the EXECUTE command.

CREATE OR REPLACE PROCEDURE public.ct_tab (vname varchar)
AS $$  
DECLARE tname VARCHAR(50):='public.swap_'||vname;

BEGIN 

execute 'create table ' || tname || ' as select ''name''';

END;
$$ LANGUAGE plpgsql 
;

现在，如果您调用传递"abc"的过程，则将在公共模式中创建一个名为"swap_abc"的表.

Now if you call the procedure passing 'abc', a table named "swap_abc" will be created in public schema.

call public.ct_tab('abc');

让我知道是否有帮助:)

Let me know if it helps :)

这篇关于如何在Redshift过程中写入动态创建的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！