AWS Redshift动态从RECORD中选择列名

Question

我在AWS红移中创建了以下过程。在query2(在????)我想根据字段输入变量中提供的值从recc中选择列。例如，如果field = 'Fname'，那么在query2中应该插入rec.Fname.

请让我知道如何从打开游标中的RECORD动态选择列名。

CREATE OR REPLACE PROCEDURE test3(source_table varchar(100), target_table varchar(100), field varchar(100) )
LANGUAGE plpgsql
AS $$   
declare
query1 text;
query2 text;
rec RECORD;
begin
query1 := 'SELECT id, ' || field ||', load_date, end_date FROM ' || source_table || ' ORDER BY id, load_date';
FOR rec IN execute query1
loop
query2 := 'insert into '|| target_table ||' values ('||quote_literal(rec.id)||', '||quote_literal(field)||','||**????**||','||quote_literal(rec.load_date)||')';
execute query2;
END LOOP;
RETURN;
END;
$$
;

Answer 1

现在还为时过早，所以让我参考一下我对类似情况(插入而不是选择)给出的答案。如何在Redshift

中连接系统表或信息架构表与用户定义的表代码如下:

CREATE OR REPLACE procedure rewrite_data()
AS
$$
DECLARE 
row record;
BEGIN
drop table if exists fred;
create table fred (schemaname varchar(256),tablename varchar(256),"column"varchar(256), "type"varchar(256));
for row in select "schemaname"::text, "tablename"::text, "column"::text, "type"::text from pg_table_def where "schemaname" <> 'pg_catalog' LOOP
INSERT INTO fred(schemaname,tablename,"column","type") VALUES (row.schemaname,row.tablename,row."column",row."type");
END LOOP;
END;
$$ LANGUAGE plpgsql;
call rewrite_data();
select * from fred;

考虑到您已经完成了存储过程的这一步，这应该可以让您越过终点线。