使用Postgres触发器记录仅修改字段的JSON

Question

有没有办法获得唯一修改的字段的JSON？

现在，我使用以下触发器，但整个行都印在ChangElog中。

示例表：

TABLE tbl_changelog (
  tbl    TEXT,
  op   TEXT,
  new     JSON,
  old     JSON
);

TABLE tbl_items (
  f1    TEXT,
  f2    TEXT
);

触发器：

CREATE OR REPLACE FUNCTION changelog_procedure() RETURNS trigger AS $$
BEGIN
INSERT INTO tbl_changelog(tbl, op, new, old)
VALUES (TG_TABLE_NAME, TG_OP, row_to_json(NEW), row_to_json(OLD));
RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER changelog_items
AFTER INSERT OR UPDATE OR DELETE ON tbl_items
FOR EACH ROW EXECUTE PROCEDURE changelog_procedure();

插入和上传的F2和F1后，ChangElog如下：

tbl_changelog
------------------------------------------------------------------
 tbl      |   op    | new                  |     old
------------------------------------------------------------------
tbl_items | INSERT  | {f1: "aa", f2: "bb"} | 
------------------------------------------------------------------
tbl_items | UPDATE  | {f1: "aa", f2: "cc"} | {f1: "aa", f2: "bb"}
------------------------------------------------------------------
tbl_items | UPDATE  | {f1: "dd", f2: "cc"} | {f1: "aa", f2: "cc"}
------------------------------------------------------------------

我只想记录更改，即：

tbl_changelog
------------------------------------------------------------------
 tbl      |   op    | new                  |     old
------------------------------------------------------------------
tbl_items | INSERT  | {f1: "aa", f2: "bb"} | 
------------------------------------------------------------------
tbl_items | UPDATE  | {f2: "cc"}           | {f2: "bb"}
------------------------------------------------------------------
tbl_items | UPDATE  | {f1: "dd"}           | {f1: "aa"}
------------------------------------------------------------------

Answer 1

您的触发功能无法正常工作，它在插入一行时会产生错误：

错误：记录“旧”尚未分配

细节：尚未分配的记录的元组结构不确定。

您应该分别处理insert的三种情况 和delete单独处理：

create or replace function changelog_procedure() 
returns trigger as $
declare
    json_new jsonb;
    json_old jsonb;
begin
    if tg_op = 'INSERT' then
        json_new:= to_jsonb(new);
    elsif tg_op = 'DELETE' then
        json_old:= to_jsonb(old);
    else
        select jsonb_object_agg(new_key, new_value), jsonb_object_agg(old_key, old_value)
        into json_new, json_old
        from jsonb_each(to_jsonb(new)) as n(new_key, new_value)
        join jsonb_each(to_jsonb(old)) as o(old_key, old_value) 
        on new_key = old_key and new_value <> old_value;
    end if;
    insert into tbl_changelog(tbl, op, new, old)
    values (tg_table_name, tg_op, json_new, json_old);
    return null;
end;
$ language plpgsql;