PostgreSQL 在触发器函数中动态修改新记录中的字段

Question

我有一个用户表，其中包含 ID 和用户名(以及其他详细信息)和其他几个引用该表的表，这些表具有各种列名(CONSTRAINT some_name FOREIGN KEY(列名)REFERENCES“用户”(userid)).我需要做的是将用户名添加到引用表(为删除整个用户表做准备)。这当然可以通过单个 ALTER TABLE 和 UPDATE 轻松完成，并且使用触发器使它们保持最新也(相当)容易。但让我有些烦恼的是触发功能。我本可以为每个表使用单独的函数，但这似乎是多余的，因此我为此创建了一个通用函数:

CREATE OR REPLACE FUNCTION public.add_username() RETURNS trigger AS
$BODY$
  DECLARE
    sourcefield text;
    targetfield text;
    username text;
    existing text;
  BEGIN
    IF (TG_NARGS != 2) THEN
      RAISE EXCEPTION 'Need source field and target field parameters';
    END IF;
    sourcefield = TG_ARGV[0];
    targetfield = TG_ARGV[1];
    EXECUTE 'SELECT username FROM "user" WHERE userid = ($1).' || sourcefield INTO username USING NEW;
    EXECUTE format('SELECT ($1).%I', targetfield) INTO existing USING NEW;
    IF ((TG_OP = 'INSERT' AND existing IS NULL) OR (TG_OP = 'UPDATE' AND (existing IS NULL OR username != existing))) THEN
      CASE targetfield
        WHEN 'username' THEN
          NEW.username := username;
        WHEN 'modifiername' THEN
          NEW.modifiername := username;
        WHEN 'creatorname' THEN
          NEW.creatorname := username;
        .....
      END CASE;
    END IF;
    RETURN NEW;
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

并使用触发函数:

CREATE TRIGGER some_trigger_name BEFORE UPDATE OR INSERT ON my_schema.my_table FOR EACH ROW EXECUTE PROCEDURE public.add_username('userid', 'username');

其工作方式是触发器函数通过 TG_ARGV 接收原始源字段名称(例如 userid)和目标字段名称(username)。然后使用这些来填充(可能)缺失的信息。所有这一切都很好用，但我怎样才能摆脱 CASE 的困惑呢？当我事先不知道字段的名称(或者更确切地说它可能是很多东西)时，有没有办法动态修改 NEW 记录中的值？它在 targetfield 参数中，但显然 NEW.targetfield 不起作用，也不像 NEW[targetfield] (例如 Javascript) .

有什么想法可以实现吗？除了使用例如 PL/Python..

Answer 1

没有简单的基于 plpgsql 的解决方案。一些可能的解决方案:

1. 使用hstore扩展名。

CREATE TYPE footype AS (a int, b int, c int);postgres=# select row(10,20,30);    row     ------------ (10,20,30)(1 row)postgres=# select row(10,20,30)::footype #= 'b=>100';  ?column?   ------------- (10,100,30)(1 row)

hstore 基于函数可以非常简单:

create or replace function update_fields(r anyelement,                                         variadic changes text[])returns anyelement as $$select $1 #= hstore($2);$$ language sql;postgres=# select *              from update_fields(row(10,20,30)::footype,                                 'b', '1000', 'c', '800'); a  |  b   |  c  ----+------+----- 10 | 1000 | 800(1 row)

1. 几年前我写了一个扩展 pl toolbox .有一个函数record_set_fields:

pavel=# select * from pst.record_expand(pst.record_set_fields(row(10,20),'f1',33)); name | value |   typ   ------+-------+--------- f1   | 33    | integer f2   | 20    | integer(2 rows)

也许你可以找到一些基于系统表和数组的一些技巧的仅 plpgsql 解决方案，如 this ，但我不能建议。它的可读性太差，对于非高级用户来说只是黑魔法。 hstore 很简单，几乎无处不在，因此它应该是首选方式。

在 PostgreSQL 9.4(也许是 9.3)上，您可以尝试使用 JSON 操作来施展魔法:

postgres=# select json_populate_record(NULL::footype, jo)               from (select json_object(array_agg(key),                                       array_agg(case key when 'b'                                                           then 1000::text                                                          else value                                                  end)) jo       from json_each_text(row_to_json(row(10,20,30)::footype))) x; json_populate_record ---------------------- (10,1000,30)(1 row)

所以我可以写函数:

CREATE OR REPLACE FUNCTION public.update_field(r anyelement,                                                fn text, val text,                                                OUT result anyelement) RETURNS anyelement LANGUAGE plpgsqlAS $function$declare jo json;begin  jo := (select json_object(array_agg(key),                             array_agg(case key when 'b' then val                                               else value end))             from json_each_text(row_to_json(r)));  result := json_populate_record(r, jo);end;$function$postgres=# select * from update_field(row(10,20,30)::footype, 'b', '1000'); a  |  b   | c  ----+------+---- 10 | 1000 | 30(1 row)

基于 JSON 的函数不应该很快。 hstore 应该更快。