~singpolyma/jmp-schemas

ref: 48bc71e185ca235f2192a2dbf6cb70dbd9de1aa1 jmp-schemas/revert/plan_log_with_range.sql -rw-r--r-- 651 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
-- Revert jmp:plan_log_with_range from pg

BEGIN;

DROP VIEW customer_plans;

ALTER TABLE plan_log ADD starts_at TIMESTAMP;
ALTER TABLE plan_log ADD expires_at TIMESTAMP;

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

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

CREATE VIEW customer_plans AS
	SELECT DISTINCT ON (customer_id)
		customer_id,
		plan_name,
		expires_at
	FROM plan_log
	WHERE starts_at <= NOW()
	ORDER BY customer_id, starts_at DESC;

COMMIT;