문제 설명
AWS Redshift는 RECORD에서 열 이름을 동적으로 선택합니다. (AWS Redshift dynamically select column name from RECORD)
AWS redshift에서 아래 절차를 생성했습니다. query2(????)에서 필드 입력 변수에 제공된 값을 기반으로 rec에서 열을 선택하고 싶습니다. 예를 들어 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;
$$
;
참조 솔루션
방법 1:
It is early here so let me just reference an answer I gave for a similar situation (inserting instead of selecting). This should get you started ‑ How to join System tables or Information Schema tables with User defined tables in Redshift
The code looks like:
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;
Given that you have gotten this far on your stored procedure this should get you over the finish line.
(by Prithvi、Bill Weiner)