Postgres SQL - Trigger with dynamic column name

Question

I wrote a trigger function with loop on the trigger table column names, I call to the function from different tables with different columns. The function insert the column names to array and loop on them in order to insert the values into another schema and tables.

The function and the trigger creation script:

DROP TRIGGER cc_experiences_insert_row ON epro.experiences;
CREATE TRIGGER cc_experiences_insert_row BEFORE INSERT ON epro.experiences FOR EACH ROW EXECUTE PROCEDURE epro.cc_update_subject_device();


CREATE OR REPLACE FUNCTION epro.cc_update_subject_device() 
RETURNS trigger AS $BODY$

DECLARE cols text[];
DECLARE table_column text;
DECLARE ordinal integer;
DECLARE v_study_event_oid character varying;
DECLARE v_item_oid character varying;
DECLARE v_crf_version_oid character varying;
DECLARE insertRow BOOLEAN;
DECLARE v_study_subject_id integer;
DECLARE v_item_id integer;
DECLARE v_crf_version_id integer;
DECLARE v_study_event_definition_id integer;
DECLARE v_study_event_id integer;
DECLARE v_event_crf_id integer;
DECLARE v_item_data_id integer;

BEGIN
    IF (TG_OP='INSERT') THEN
        select study_subject_id from public.study_subject where label=NEW.subject_label INTO v_study_event_oid;
        select array(select column_name::text from information_schema.columns  where table_name = TG_TABLE_NAME and column_name not in ('id','name','subject_label','created','modified')) INTO cols;

        FOR I IN array_lower(cols, 1)..array_upper(cols, 1) LOOP

            select item_oid from epro.edc_epro_data_mappings where name = TG_TABLE_NAME and col = cols[I] INTO v_item_oid;
            select crf_version_oid from epro.edc_epro_data_mappings where name = TG_TABLE_NAME and col = cols[I] INTO v_crf_version_oid;
            select study_event_definition_oid from epro.edc_epro_data_mappings where name = TG_TABLE_NAME and col = cols[I] INTO v_study_event_oid;

            select item_id from public.item where oc_oid = v_item_oid INTO v_item_id;
            select crf_version_id from public.crf_version where oc_oid = v_crf_version_oid  INTO v_crf_version_id;
            select study_event_definition_id from public.study_event_definition where oc_oid = v_study_event_oid INTO v_study_event_definition_id;

            SELECT nextval('study_event_study_event_id_seq') INTO v_study_event_id;

            INSERT INTO public.study_event  
            (study_event_id,study_event_definition_id,study_subject_id,date_start,date_end,date_created,date_updated,update_id,subject_event_status_id,start_time_flag,end_time_flag,prev_subject_event_status,owner_id,status_id) 
            VALUES(v_study_event_id,v_study_event_definition_id,v_study_subject_id,now(),now(),now(),now(),1,4,'f','f',1,1,1);

            SELECT nextval('event_crf_event_crf_id_seq') INTO v_event_crf_id;

            INSERT INTO public.event_crf  
            (event_crf_id,study_event_id,crf_version_id,completion_status_id,date_completed,owner_id,status_id,date_created,study_subject_id,date_updated) 
            VALUES(v_event_crf_id,v_study_event_id,v_crf_version_id,1,now(),1,1,now(),v_study_subject_id,now());

            --SELECT ordinal from public.item_data

            SELECT nextval('item_data_item_data_id_seq') INTO v_item_data_id;

            table_column = 'NEW.'||cols[I];
            EXECUTE 'INSERT INTO public.item_data(item_data_id ,item_id , event_crf_id ,status_id , value , date_created, owner_id)
            VALUES($1, $2 , $3 , 1, $4 , now(), 1)'
            USING v_item_data_id,v_item_id,v_event_crf_id,NEW.cols[I];

            /*INSERT INTO public.item_data
            (item_data_id ,item_id , event_crf_id ,status_id , value , date_created, owner_id)
            VALUES(v_item_data_id, v_item_id , v_event_crf_id , 1, table_column , now(), 1); */ 
        END LOOP;
    END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION epro.cc_update_subject_device()
  OWNER TO postgres;

The running was successfully, But when I tried to insert data I got the following error:

ERROR: record "new" has no field "cols" CONTEXT: SQL statement "SELECT NEW.cols[I]" PL/pgSQL function epro.cc_update_subject_device() line 50 at EXECUTE

How can I run on the tables' columns dynamically?

Thanks,

Answer 1

easier to use row_to_json

            FOR I IN array_lower(cols, 1)..array_upper(cols, 1) LOOP
                RAISE NOTICE '% % %', I, cols[I], row_to_json(NEW)->>cols[I];
            end loop;

output

1 id 120085
2 created_by 15615323
...

or

FOR col IN SELECT column_name
           FROM information_schema.columns
           WHERE table_name = 'your_table'
    LOOP
        raise notice '% %', col, row_to_json(NEW)->col;

 id 120085
 created_by 15615323