0

I have created below procedure in AWS redshift. In the query2 (at ????) I want to select the column from rec based on value provided in field input variable. e.g. if field = 'Fname' then in query2 it should insert rec.Fname.

Please let me know how to select column names dynamically from RECORD in open cursor.

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;
$$
;
General Grievance
  • 4,555
  • 31
  • 31
  • 45
Prithvi
  • 1
  • 1

1 Answers1

0

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.

Bill Weiner
  • 8,835
  • 2
  • 7
  • 18
  • Hi Bill, Thanks for you response. I tried to use this, however couldn’t reach where I want to. I want value of "column" in row."column" to be dynamic, based on value provided in INPUT parameter. In above query, field names in row are fixed. Is there any way we can create a dynamic SQL which can use variable/parameter value to select field in open record. – Prithvi Oct 11 '21 at 12:35
  • In rewriting this you can't use the variable as a column or table name directly in a stored procedure query. Instead you need to compose a query string and execute that. Like - "EXECUTE 'SELECT id, ' || field ||', load_date, end_date FROM ' || source_table || ' ORDER BY id, load_date';". – Bill Weiner Oct 11 '21 at 13:24
  • Thanks Bill! I got a solution, my challenge here got resolved by renaming the input column_name in query1 :) I can use that renamed field in query2, no need to populate it dynamically. Thanks for your help. – Prithvi Oct 13 '21 at 04:24