postgresql - 如何在 PostgreSQL 中存储表历史记录

Question

设想

我们需要在 PostgreSQL 中存储记录历史，这样当一条记录插入或更新到主表中时(例如: pets )，它会自动备份到历史表( 0x1040405)。

理想情况下，我们需要根据主表的模式生成历史表，无需任何人工干预。

INSERT INTO pets(name, species) VALUES ('Meowth', 'Cat')

pets:
+---+------------+-------------+
|id | name | species |
+---+------------+-------------+
| 1 | Meowth | Cat |
+---+------------+-------------+

A pets_history 应自动将记录插入 Trigger :

pets_history:
+----+--------+-----------+---------+
| id | ref_id | name      | species |
+----+--------+-----------+---------+
| 1  | 1      | Meowth    | Cat     |
+----+--------+-----------+---------+

当对宠物进行更新以将我的猫的名字从 pets_history 更改为 Meowth 时。例如:

UPDATE pets SET name = 'Persian' WHERE id = 1;

pets:
+---+------------+-------------+
|id | name       | species     |
+---+------------+-------------+
| 1 | Persian    | Cat         |
+---+------------+-------------+

我想结束以下...

pets_history:
+----+--------+-----------+---------+
| id | ref_id | name      | species |
+----+--------+-----------+---------+
| 1  | 1      | Meowth    | Cat     |
| 2  | 1      | Persian   | Cat     |
+----+--------+-----------+---------+

稍后将另一列/字段添加到 Persian 表时，例如: pets

pets:
+---+--------+---------+-------+
|id | name   | species | color |
+---+--------+---------+-------+
| 1 | Meowth | Cat     | cream |
+---+--------+---------+-------+

我们希望这能自动反射(reflect)在 color 表中:

pets_history:
+----+--------+---------+---------+-------+
| id | ref_id | name    | species | color |
+----+--------+---------+---------+-------+
| 1  | 1      | Meowth  | Cat     | null  |
| 2  | 1      | Persian | Cat     | null  |
| 3  | 1      | Persian | Cat     | cream |
+----+--------+---------+---------+-------+

如果有人知道在 PostgreSQL 中本地执行此操作的任何方法或以其他方式执行此操作，请分享。
我们查看了这个问题/答案 Implementing history of PostgreSQL table，它部分解决了挑战，但它不会自动创建 pets_history 表。

Answer 1

您可以使用 to_jsonb将整行作为 JSON 对象保留在历史表中。在这种情况下，您不需要关心在历史表中添加新列，因为值的键将是列名。
宠物 table

CREATE TABLE public.pets
(
  id serial NOT NULL,
  name text,
  species text,
  PRIMARY KEY (id)
);

宠物历史表

CREATE TABLE public.h_pets
(
  id serial NOT NULL,
  target_row_id integer NOT NULL,
  executed_operation integer NOT NULL,
  operation_executed_at timestamp without time zone NOT NULL DEFAULT now(),
  data_after_executed_operation jsonb,
  PRIMARY KEY (id)
);

向历史表中添加行的函数

CREATE OR REPLACE FUNCTION public.on_content_change()
  RETURNS trigger
  LANGUAGE 'plpgsql'
AS $BODY$
  DECLARE
    target_history_table TEXT;
  BEGIN
    target_history_table := TG_ARGV[0];

    IF TG_OP = 'INSERT'
    THEN
      EXECUTE
        format(
          'INSERT INTO %I (target_row_id, executed_operation, data_after_executed_operation) VALUES ($1.id, 0, to_jsonb($1))',
          target_history_table
        )
        USING NEW;
      RETURN NEW;
    ELSIF TG_OP = 'UPDATE'
    THEN
      EXECUTE
        format(
          'INSERT INTO %I (target_row_id, executed_operation, data_after_executed_operation) VALUES ($1.id, 1, to_jsonb($1))',
          target_history_table
        )
        USING NEW;
      RETURN NEW;
    ELSIF TG_OP = 'DELETE'
    THEN
      EXECUTE
        format(
          'INSERT INTO %I (target_row_id, executed_operation) VALUES ($1.id, 2)',
          target_history_table
        )
        USING OLD;
      RETURN OLD;
    END IF;
  END;
$BODY$;

和宠物表的触发器

CREATE TRIGGER pets_history_trigger
  BEFORE INSERT OR DELETE OR UPDATE
  ON public.pets
  FOR EACH ROW
  EXECUTE PROCEDURE public.on_content_change('h_pets');