sql - Postgres 触发器将许多列组合成一个 JSON 列

Question

我想在表上创建一个 Postgres 触发器，以便在插入或更新行时运行。该表有很多列，我希望触发器将该行插入到另一个表中。但是在另一张表中，所有这些列都应该组合成一个 JSON 对象(在较新版本的 Postgres 中为 JSONB)。

原始表格

column1|column2|column3 |
-------|-------|--------|
A      |B      |C       |

新建表格

combined_column                        |
---------------------------------------|
{ column1: A, column2: B, column3: C } |

因此，创建触发器的表将具有例如 3 列，但触发器插入的表将只有 1 列(一个 JSON 对象组合了原始表中插入/更新行的所有列).

Answer 1

以原始形式保存行会更有效率。无需改造，占用磁盘空间少，更快，更干净。

只需创建一个具有相同结构的日志表:

CREATE TABLE tbl_log AS TABLE tbl LIMIT 0;

或者使用LIKE keyword使用 INCLUDING 子句更详细地指定从原始文件中获取的内容。示例:

CREATE TABLE tbl_log (LIKE tbl INCLUDING STORAGE);

触发函数:

CREATE OR REPLACE FUNCTION trg_tbl_log()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO tbl_log VALUES (NEW.*);
   RETURN NEW;
END
$func$;

触发器:

CREATE TRIGGER tbl_log
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_tbl_log();

在 Postgres 11 或更高版本中，使用更简洁的语法:

...
FOR EACH ROW EXECUTE FUNCTION trg_tbl_log();

如果需要，您可以使用 row_to_json() 轻松地将行转换为 json 值。或者更简单，只是 to_json()。最好使用 to_jsonb() 并保存 jsonb 而不是 json:

...
INSERT INTO generic_js_log (json_column) SELECT to_jsonb(NEW);
...

JSON functions in the manual.