python - Postgresql 触发器检查哪些列的值发生了变化？

Question

需要在使用 postgresql(v.9.5) 触发器更新之前找到行中哪些列的值已更改，根据列名，必须返回 Null 或 NEW。当表格的列数很多时如何实现？

Answer 1

在触发器 FOR EACH ROW 中使用 json。注意:如果旧/新值可能为 NULL，请使用 IS DISTINCT FROM。

CREATE OR REPLACE FUNCTION public.test_tr_old_new()
  RETURNS trigger AS $$
DECLARE
  _rec record;
BEGIN
  FOR _rec IN
    SELECT
      o.key
      , o.value AS old_value
      , n.value AS new_value
    FROM json_each(to_json(new)) n
      INNER JOIN json_each(to_json(old)) o ON o.key = n.key
  LOOP
    IF (_rec.old_value::text IS DISTINCT FROM _rec.new_value::text) THEN
      RAISE NOTICE 'test_tr_old_new(): Column % changed from ''%'' to ''%''', _rec.key, _rec.old_value, _rec.new_value;
    END IF;
  END LOOP;

  RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER old_new
  BEFORE UPDATE
  ON public._tr_table
  FOR EACH ROW
EXECUTE PROCEDURE public.test_tr_old_new();