Postgres 函数用于在更新后以 JSON 形式显示新列值

Question

我正在尝试为触发器编写一个 postgres 函数AFTER UPDATE该触发器创建一个 JSON 对象，该对象仅针对更改的列创建具有列名称及其更新值的 JSON 对象。我正在尝试创建一个可用于任何表的通用函数，因此使用Record作为函数参数。old和new来自触发器的OLD和NEW变量。最终目标是将返回的 JSON 保存到审核表中的 JSON 字段中。

CREATE OR REPLACE FUNCTION row_updates(old RECORD, new RECORD) RETURNS JSON AS
$$
DECLARE
updates JSON;
BEGIN
WITH columns AS (
SELECT json_object_keys(row_to_json(new)) "column"
)
SELECT
json_object_agg("column", new_value) INTO updates
FROM (
SELECT 
"column",
(row_to_json(new)->"column" #>> '{}') as new_value,
(row_to_json(old)->"column" #>> '{}') as old_value
FROM
columns
) changes
WHERE 
new_value IS DISTINCT FROM old_value;
RETURN updates;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION audit_change() RETURNS TRIGGER AS
$$
DECLARE
updates JSON;
BEGIN
IF (TG_OP = 'INSERT') THEN
raise NOTICE 'Logging insert on relation (%.%) %', TG_TABLE_SCHEMA, TG_TABLE_NAME;
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
updates := row_updates(OLD, NEW)
raise NOTICE 'Logging update on relation (%.%) %', TG_TABLE_SCHEMA, TG_TABLE_NAME, updates;
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
raise NOTICE 'Logging delete on relation (%.%) %', TG_TABLE_SCHEMA, TG_TABLE_NAME;
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;

我收到此错误：ERROR: PL/pgSQL functions cannot accept type record.

有没有更好的方法来实现这一点？显然，我想在非触发器函数中执行此操作，以便我可以在不同表的不同触发器中重用它。此外，如果可能的话，我可能希望将其调整为AFTER INSERT和AFTER DELETE触发器，因此我不想在触发器函数中分支TG_OP并重复上面SQL中显示的逻辑。

最终目标：

=> select * from org_user;
id | org_id | user_id 
----+--------+---------
1 |      1 |       1
23 |      1 |       3
=> update org_user set org_id=3, user_id = 4 where id = 23; 
-- trigger creates following row in audit table
id | relation  | record_id | updates 
----+-----------+-----------+--------------
1 |  org_user |     23    | {'org_id': 3, 'user_id': 4} 

Answer 1

创建一个比较两个JSONB值的函数：

create or replace function jsonb_diff(jsonb, jsonb)
returns jsonb language sql immutable as $$
select jsonb_object_agg(n.key, n.value)
from jsonb_each($1) o
join jsonb_each($2) n on o.key = n.key
where o.value <> n.value;
$$;

并在触发函数中使用它：

updates := jsonb_diff(to_jsonb(OLD), to_jsonb(NEW));
raise NOTICE 'Logging update on relation (%.%) %', TG_TABLE_SCHEMA, TG_TABLE_NAME, updates;
RETURN NEW;

顺便说一下，在 Postgres 11+ 中，您可以使用record类型的函数参数。