table_version

pig build pkg table_version;		# build deb

pig repo add pgsql -u   # add both repo and update cache

pig install table_version;		# install via package name, for the active PG version

pig install table_version -v 18;   # install for PG 18
pig install table_version -v 17;   # install for PG 17
pig install table_version -v 16;   # install for PG 16
pig install table_version -v 15;   # install for PG 15
pig install table_version -v 14;   # install for PG 14

CREATE EXTENSION table_version CASCADE; -- requires 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
);

-- Enable versioning
SELECT table_version.ver_enable_versioning('foo', 'bar');

-- Create a revision and insert data
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();

-- Show differences between revisions
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. Determine which tables are versioned
SELECT * FROM table_version.ver_get_versioned_tables();

-- 2. Get the base revision
SELECT table_version.ver_get_table_base_revision('foo', 'bar');

-- 3. Create a base snapshot
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. Get differences to apply incremental updates
SELECT * FROM table_version.ver_get_foo_bar_diff(
    my_last_revision,
    table_version.ver_get_table_last_revision('foo', 'bar')
);