g7gSpehV

1DROP VIEW IF EXISTS my_loan_all;
2DROP TABLE IF EXISTS my_loan;
3DROP TABLE IF EXISTS my_loan_history;
4DROP SEQUENCE IF EXISTS my_loan_seq;
5CREATE SEQUENCE my_loan_seq;
6
7CREATE TABLE my_loan
8(
9   my_loan_id BIGINT NOT NULL DEFAULT nextval('my_loan_seq')
10  ,account_number VARCHAR(100) NOT NULL
11  ,amount_due DECIMAL(10,2)
12  ,status TEXT NOT NULL
13  ,valid_period tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, null)
14  ,entity_updated_by VARCHAR(255) NOT NULL DEFAULT CURRENT_USER -- users account
15  ,entity_updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp() -- current timestamp
16);
17ALTER TABLE my_loan ADD CONSTRAINT my_loan_int
18  EXCLUDE USING GIST (account_number with = , valid_period WITH &&); -- ranges cannot overlap
19CREATE TABLE my_loan_history (LIKE my_loan);
20ALTER TABLE my_loan ADD COLUMN given_timestamp timestamptz; -- there must be such a column if we need to provide ts in insert
21ALTER TABLE my_loan ADD PRIMARY KEY (my_loan_id); -- must be done after history table is created
22TRUNCATE my_loan; TRUNCATE my_loan_history;
23DROP TRIGGER  IF EXISTS my_versioning_trigger ON my_loan;
24CREATE TRIGGER my_versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON my_loan
25  FOR EACH ROW EXECUTE PROCEDURE my_versioning (
26  -- where to store validity, what is history table called, what column provides business date (now)
27    'valid_period',           'my_loan_history',            'given_timestamp',                      true
28);
29
30INSERT INTO my_loan (account_number, amount_due, status, given_timestamp) VALUES ('123001', '100', 'inserted', '2019-04-08 11:00:00.000000+01');
31UPDATE my_loan SET status = 'updated',  amount_due=110, given_timestamp='2019-04-09 12:00:00.000000+01' WHERE account_number = '123001';
32UPDATE my_loan SET status = 'updated 2nd time', given_timestamp='2019-04-09 13:00:00.000000+01' WHERE account_number = '123001';
33SELECT * FROM my_loan;
34SELECT * FROM my_loan_history;
35SELECT * FROM my_loan;
36CREATE VIEW my_loan_all AS SELECT * FROM my_loan UNION ALL SELECT *, NULL AS given_timestamp FROM my_loan_history;
37SELECT * FROM my_loan_all WHERE valid_period @> '2019-04-09 10:00:05.000000+01'::timestamptz ;
38SELECT * FROM my_loan_all WHERE valid_period @> '2019-04-09 13:00:05.000000+01'::timestamptz ;
39SELECT * FROM my_loan_all;
40
41UPDATE my_loan SET status = 'update will fail', given_timestamp='2019-04-09 12:30:00.000000+01' WHERE account_number = '123001'; -- late arriving dimension - we can reject or put aside to fix later
42 set session.closing_timestamp to '2019-04-10 13:34:30.825804+01'; -- we must provide closing business timestamp somehow
43show session.closing_timestamp;
44DELETE FROM my_loan WHERE account_number = '123001';
45
46SELECT version();