table_version

pig build pkg table_version         # 构建 DEB 包

pig repo add pgsql -u          # 添加仓库并更新缓存

CREATE EXTENSION table_version CASCADE;  -- 依赖: plpgsql

CREATE EXTENSION table_version;

CREATE SCHEMA foo;
SET search_path TO foo, public;

CREATE TABLE foo.bar (
    id INTEGER NOT NULL PRIMARY KEY,
    baz TEXT
);

-- 启用版本控制
SELECT table_version.ver_enable_versioning('foo', 'bar');

-- 创建版本并插入数据
SELECT table_version.ver_create_revision('Insert data');
INSERT INTO foo.bar (id, baz) VALUES
  (1, 'foo bar 1'),
  (2, 'foo bar 2'),
  (3, 'foo bar 3');
SELECT table_version.ver_complete_revision();

-- 查看两个版本之间的差异
SELECT * FROM table_version.ver_get_foo_bar_diff(1001, 1002);

SELECT table_version.ver_enable_versioning('foo', 'bar');

BEGIN;
INSERT INTO foo.bar (id, baz) VALUES (1, 'foo bar 1');
INSERT INTO foo.bar (id, baz) VALUES (2, 'foo bar 2');
COMMIT;

BEGIN;
UPDATE foo.bar SET baz = 'foo bar 1 edit' WHERE id = 1;
COMMIT;

SELECT * FROM table_version.foo_bar_revision;

-- 1. 确定哪些表已启用版本控制
SELECT * FROM table_version.ver_get_versioned_tables();

-- 2. 获取基准版本
SELECT table_version.ver_get_table_base_revision('foo', 'bar');

-- 3. 创建基准快照
CREATE TABLE foo_bar_copy AS
SELECT * FROM table_version.ver_get_foo_bar_revision(
    table_version.ver_get_table_base_revision('foo', 'bar')
);

-- 4. 获取差异以进行增量更新
SELECT * FROM table_version.ver_get_foo_bar_diff(
    my_last_revision,
    table_version.ver_get_table_last_revision('foo', 'bar')
);