postgresql - Postgresql - 使用动态列名触发

Question

我写了一个触发器函数，在触发器表列名上循环，我从具有不同列的不同表调用该函数。该函数将列名插入到数组中并在它们上循环，以便将值插入到另一个模式和表中。

函数和触发器创建脚本:

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;

运行成功，但是插入数据时出现如下错误:

错误:记录“new”没有字段“cols”上下文:SQL 语句“SELECT NEW.cols[I]”PL/pgSQL 函数 epro.cc_update_subject_device() 第 50 行 EXECUTE

如何动态地在表的列上运行？

谢谢，

Answer 1

如果您想将列名插入到表 public.item_data 中，那么您只需在 INSERT 中使用 cols[I] > 语句，但没有 EXECUTE 语句，因为查询没有任何动态:

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, 'NEW.' || cols[I], now(), 1);

如果您想插入列的值而不是列的名称，那么您首先必须通过动态查询获取该值:

EXECUTE format('SELECT NEW.$I', cols[I]) INTO table_column;
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);