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;