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行执行

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

谢谢。

- MRah

---

你不能使用 plpgsql 来做那件事。不过，你可以使用 pltcl，plperl 或其他的方法来实现。你还可以选择使用 json/jsonb，而不是你正在尝试做的事情。 - Ezequiel Tolnay

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);

- Patrick

---

谢谢你的回答，我使用 NEW.* 参数构建了另一个数组，并从中选择了值。谢谢。 - MRah

我尝试使用您的答案代码，但我在字段表中得到以下内容：“NEW.discomfort_id” - 字段名称而不是字段值（如5）。 - MRah

谢谢，但是我得到了以下错误：ERROR: 语法错误，在"$"附近 LINE 1: SELECT NEW .$ I - MRah

Answer 2

更易于使用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;

输出

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

- des1roer