SET client_min_messages TO NOTICE;
SET TIME ZONE 'UTC';
CREATE TABLE versioning (a bigint, "b b" date, sys_period tstzrange);
-- Insert some data before versioning is enabled.
INSERT INTO versioning (a, sys_period) VALUES (1, tstzrange('-infinity', NULL));
INSERT INTO versioning (a, sys_period) VALUES (2, tstzrange('2000-01-01', NULL));
CREATE TABLE versioning_history (a bigint, c date, sys_period tstzrange);
CREATE TRIGGER versioning_trigger
BEFORE INSERT OR UPDATE OR DELETE ON versioning
FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'versioning_history', false);
-- Insert.
BEGIN;
SELECT set_system_time('2001-01-01'::timestamptz);
 set_system_time 
-----------------
 
(1 row)

INSERT INTO versioning (a) VALUES (3);
SELECT a, "b b", sys_period FROM versioning ORDER BY a, sys_period;
 a | b b |            sys_period             
---+-----+-----------------------------------
 1 |     | [-infinity,)
 2 |     | ["Sat Jan 01 00:00:00 2000 UTC",)
 3 |     | ["Mon Jan 01 00:00:00 2001 UTC",)
(3 rows)

SELECT * FROM versioning_history ORDER BY a, sys_period;
 a | c | sys_period 
---+---+------------
(0 rows)

COMMIT;
-- Update.
BEGIN;
SELECT set_system_time('2001-02-01');
 set_system_time 
-----------------
 
(1 row)

UPDATE versioning SET a = 4 WHERE a = 3;
SELECT a, "b b", sys_period FROM versioning ORDER BY a, sys_period;
 a | b b |            sys_period             
---+-----+-----------------------------------
 1 |     | [-infinity,)
 2 |     | ["Sat Jan 01 00:00:00 2000 UTC",)
 4 |     | ["Thu Feb 01 00:00:00 2001 UTC",)
(3 rows)

SELECT a, c, sys_period FROM versioning_history ORDER BY a, sys_period;
 a | c |                           sys_period                            
---+---+-----------------------------------------------------------------
 3 |   | ["Mon Jan 01 00:00:00 2001 UTC","Thu Feb 01 00:00:00 2001 UTC")
(1 row)

COMMIT;
-- Multiple updates.
BEGIN;
SELECT set_system_time('2001-03-01');
 set_system_time 
-----------------
 
(1 row)

UPDATE versioning SET a = 5 WHERE a = 4;
UPDATE versioning SET "b b" = '2012-01-01' WHERE a = 5;
SELECT a, "b b", sys_period FROM versioning ORDER BY a, sys_period;
 a |    b b     |            sys_period             
---+------------+-----------------------------------
 1 |            | [-infinity,)
 2 |            | ["Sat Jan 01 00:00:00 2000 UTC",)
 5 | 01-01-2012 | ["Thu Mar 01 00:00:00 2001 UTC",)
(3 rows)

SELECT a, c, sys_period FROM versioning_history ORDER BY a, sys_period;
 a | c |                           sys_period                            
---+---+-----------------------------------------------------------------
 3 |   | ["Mon Jan 01 00:00:00 2001 UTC","Thu Feb 01 00:00:00 2001 UTC")
 4 |   | ["Thu Feb 01 00:00:00 2001 UTC","Thu Mar 01 00:00:00 2001 UTC")
(2 rows)

COMMIT;
-- Delete.
BEGIN;
SELECT set_system_time('2001-04-01');
 set_system_time 
-----------------
 
(1 row)

DELETE FROM versioning WHERE a = 4;
SELECT a, "b b", sys_period FROM versioning ORDER BY a, sys_period;
 a |    b b     |            sys_period             
---+------------+-----------------------------------
 1 |            | [-infinity,)
 2 |            | ["Sat Jan 01 00:00:00 2000 UTC",)
 5 | 01-01-2012 | ["Thu Mar 01 00:00:00 2001 UTC",)
(3 rows)

SELECT a, c, sys_period FROM versioning_history ORDER BY a, sys_period;
 a | c |                           sys_period                            
---+---+-----------------------------------------------------------------
 3 |   | ["Mon Jan 01 00:00:00 2001 UTC","Thu Feb 01 00:00:00 2001 UTC")
 4 |   | ["Thu Feb 01 00:00:00 2001 UTC","Thu Mar 01 00:00:00 2001 UTC")
(2 rows)

COMMIT;
-- Persist on commit.
BEGIN;
DELETE FROM versioning WHERE a = 4;
SELECT * FROM versioning WHERE a = 4;
 a | b b | sys_period 
---+-----+------------
(0 rows)

SELECT * FROM versioning_history WHERE a = 4;
 a | c |                           sys_period                            
---+---+-----------------------------------------------------------------
 4 |   | ["Thu Feb 01 00:00:00 2001 UTC","Thu Mar 01 00:00:00 2001 UTC")
(1 row)

ROLLBACK;
-- Reset on rollback.
BEGIN;
SELECT set_system_time(NULL);
 set_system_time 
-----------------
 
(1 row)

ROLLBACK;
BEGIN;
DELETE FROM versioning WHERE a = 4;
SELECT * FROM versioning WHERE a = 4;
 a | b b | sys_period 
---+-----+------------
(0 rows)

SELECT * FROM versioning_history WHERE a = 4;
 a | c |                           sys_period                            
---+---+-----------------------------------------------------------------
 4 |   | ["Thu Feb 01 00:00:00 2001 UTC","Thu Mar 01 00:00:00 2001 UTC")
(1 row)

COMMIT;
-- Savepoints.
BEGIN;
SELECT set_system_time(NULL);
 set_system_time 
-----------------
 
(1 row)

SAVEPOINT p0;
SELECT set_system_time('2002-01-01');
 set_system_time 
-----------------
 
(1 row)

SAVEPOINT p1;
SELECT set_system_time('2002-02-01');
 set_system_time 
-----------------
 
(1 row)

INSERT INTO versioning (a) VALUES (6);
SELECT * FROM versioning WHERE a = 6;
 a | b b |            sys_period             
---+-----+-----------------------------------
 6 |     | ["Fri Feb 01 00:00:00 2002 UTC",)
(1 row)

SAVEPOINT p2;
SELECT set_system_time('2002-03-01');
 set_system_time 
-----------------
 
(1 row)

INSERT INTO versioning (a) VALUES (7);
SELECT * FROM versioning WHERE a = 7;
 a | b b |            sys_period             
---+-----+-----------------------------------
 7 |     | ["Fri Mar 01 00:00:00 2002 UTC",)
(1 row)

ROLLBACK TO SAVEPOINT p2;
INSERT INTO versioning (a) VALUES (7);
SELECT * FROM versioning WHERE a = 7;
 a | b b |            sys_period             
---+-----+-----------------------------------
 7 |     | ["Fri Feb 01 00:00:00 2002 UTC",)
(1 row)

ROLLBACK TO SAVEPOINT p0;
INSERT INTO versioning (a) VALUES (6);
SELECT upper(sys_period) FROM versioning WHERE a = 6 AND lower(sys_period) = CURRENT_TIMESTAMP;
 upper 
-------
 
(1 row)

END;
DROP TABLE versioning;
DROP TABLE versioning_history;