versioning.out

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;
INSERT INTO versioning (a) VALUES (3);
SELECT a, "b b", lower(sys_period) = CURRENT_TIMESTAMP FROM versioning ORDER BY a, sys_period;
 a | b b | ?column? 
---+-----+----------
 1 |     | f
 2 |     | f
 3 |     | t
(3 rows)

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

COMMIT;
-- Update.
BEGIN;
UPDATE versioning SET a = 4 WHERE a = 3;
SELECT a, "b b", lower(sys_period) = CURRENT_TIMESTAMP FROM versioning ORDER BY a, sys_period;
 a | b b | ?column? 
---+-----+----------
 1 |     | f
 2 |     | f
 4 |     | t
(3 rows)

SELECT a, c, upper(sys_period) = CURRENT_TIMESTAMP FROM versioning_history ORDER BY a, sys_period;
 a | c | ?column? 
---+---+----------
 3 |   | t
(1 row)

SELECT a, "b b" FROM versioning WHERE lower(sys_period) = CURRENT_TIMESTAMP ORDER BY a, sys_period;
 a | b b 
---+-----
 4 | 
(1 row)

COMMIT;
-- Multiple updates.
BEGIN;
UPDATE versioning SET a = 5 WHERE a = 4;
UPDATE versioning SET "b b" = '2012-01-01' WHERE a = 5;
SELECT a, "b b", lower(sys_period) = CURRENT_TIMESTAMP FROM versioning ORDER BY a, sys_period;
 a |    b b     | ?column? 
---+------------+----------
 1 |            | f
 2 |            | f
 5 | 01-01-2012 | t
(3 rows)

SELECT a, c, upper(sys_period) = CURRENT_TIMESTAMP FROM versioning_history ORDER BY a, sys_period;
 a | c | ?column? 
---+---+----------
 3 |   | f
 4 |   | t
(2 rows)

SELECT a, "b b" FROM versioning WHERE lower(sys_period) = CURRENT_TIMESTAMP ORDER BY a, sys_period;
 a |    b b     
---+------------
 5 | 01-01-2012
(1 row)

COMMIT;
-- Delete.
BEGIN;
DELETE FROM versioning WHERE a = 4;
SELECT a, "b b", lower(sys_period) = CURRENT_TIMESTAMP FROM versioning ORDER BY a, sys_period;
 a |    b b     | ?column? 
---+------------+----------
 1 |            | f
 2 |            | f
 5 | 01-01-2012 | f
(3 rows)

SELECT a, c, upper(sys_period) = CURRENT_TIMESTAMP FROM versioning_history ORDER BY a, sys_period;
 a | c | ?column? 
---+---+----------
 3 |   | f
 4 |   | f
(2 rows)

SELECT a, "b b" FROM versioning WHERE lower(sys_period) = CURRENT_TIMESTAMP ORDER BY a, sys_period;
 a | b b 
---+-----
(0 rows)

END;
-- Delete.
BEGIN;
DELETE FROM versioning;
SELECT * FROM versioning;
 a | b b | sys_period 
---+-----+------------
(0 rows)

SELECT a, c, upper(sys_period) = CURRENT_TIMESTAMP FROM versioning_history ORDER BY a, sys_period;
 a | c | ?column? 
---+---+----------
 1 |   | t
 2 |   | t
 3 |   | f
 4 |   | f
 5 |   | t
(5 rows)

SELECT a, "b b" FROM versioning WHERE lower(sys_period) = CURRENT_TIMESTAMP ORDER BY a, sys_period;
 a | b b 
---+-----
(0 rows)

END;