~singpolyma/jmp-schemas

ref: 48bc71e185ca235f2192a2dbf6cb70dbd9de1aa1 jmp-schemas/deploy/plan_log_with_range.sql -rw-r--r-- 779 bytes
48bc71e1Stephen Paul Weber sqitch tag 1 year, 6 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Deploy jmp:plan_log_with_range to pg
-- requires: plan_log
-- requires: customer_plans

BEGIN;

DROP VIEW customer_plans;

ALTER TABLE plan_log ADD date_range tsrange;

UPDATE plan_log SET date_range=tsrange(starts_at, expires_at);
ALTER TABLE plan_log ALTER date_range SET NOT NULL;

ALTER TABLE plan_log DROP starts_at;
ALTER TABLE plan_log DROP expires_at;
ALTER TABLE plan_log ADD PRIMARY KEY (customer_id, date_range);

ALTER TABLE plan_log ADD EXCLUDE USING GIST
	(customer_id WITH =, date_range WITH &&);

CREATE VIEW customer_plans AS
	SELECT DISTINCT ON (customer_id)
		customer_id,
		plan_name,
		upper(date_range) AS expires_at
	FROM plan_log
	WHERE NOT (tsrange(LOCALTIMESTAMP, LOCALTIMESTAMP, '[]') << date_range)
	ORDER BY customer_id, date_range DESC;

COMMIT;