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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
|
SELECT setting::integer < 100000 AS pre_10,
setting::integer < 120000 AS pre_12
FROM pg_settings WHERE name = 'server_version_num';
pre_10 | pre_12
--------+--------
f | t
(1 row)
/* Run tests as unprivileged user */
SET ROLE TO periods_unprivileged_user;
/*
* Create a sequence to test non-serial primary keys. This actually tests
* things like uuid primary keys, but makes for reproducible test cases.
*/
CREATE SEQUENCE pricing_seq;
CREATE TABLE pricing (id1 bigserial,
id2 bigint PRIMARY KEY DEFAULT nextval('pricing_seq'),
id3 bigint GENERATED ALWAYS AS IDENTITY,
id4 bigint GENERATED ALWAYS AS (id1 + id2) STORED,
product text, min_quantity integer, max_quantity integer, price numeric);
ERROR: syntax error at or near "("
LINE 4: ... id4 bigint GENERATED ALWAYS AS (id1 + id2...
^
CREATE TABLE pricing (id1 bigserial,
id2 bigint PRIMARY KEY DEFAULT nextval('pricing_seq'),
id3 bigint GENERATED ALWAYS AS IDENTITY,
product text, min_quantity integer, max_quantity integer, price numeric);
CREATE TABLE pricing (id1 bigserial,
id2 bigint PRIMARY KEY DEFAULT nextval('pricing_seq'),
product text, min_quantity integer, max_quantity integer, price numeric);
ERROR: relation "pricing" already exists
SELECT periods.add_period('pricing', 'quantities', 'min_quantity', 'max_quantity');
add_period
------------
t
(1 row)
SELECT periods.add_for_portion_view('pricing', 'quantities');
add_for_portion_view
----------------------
t
(1 row)
TABLE periods.for_portion_views;
table_name | period_name | view_name | trigger_name
------------+-------------+------------------------------------+---------------------------
pricing | quantities | pricing__for_portion_of_quantities | for_portion_of_quantities
(1 row)
/* Test UPDATE FOR PORTION */
INSERT INTO pricing (product, min_quantity, max_quantity, price) VALUES ('Trinket', 1, 20, 200);
TABLE pricing ORDER BY min_quantity;
id1 | id2 | id3 | product | min_quantity | max_quantity | price
-----+-----+-----+---------+--------------+--------------+-------
1 | 1 | 1 | Trinket | 1 | 20 | 200
(1 row)
-- UPDATE fully preceding
UPDATE pricing__for_portion_of_quantities SET min_quantity = 0, max_quantity = 1, price = 0;
TABLE pricing ORDER BY min_quantity;
id1 | id2 | id3 | product | min_quantity | max_quantity | price
-----+-----+-----+---------+--------------+--------------+-------
1 | 1 | 1 | Trinket | 1 | 20 | 200
(1 row)
-- UPDATE fully succeeding
UPDATE pricing__for_portion_of_quantities SET min_quantity = 30, max_quantity = 50, price = 0;
TABLE pricing ORDER BY min_quantity;
id1 | id2 | id3 | product | min_quantity | max_quantity | price
-----+-----+-----+---------+--------------+--------------+-------
1 | 1 | 1 | Trinket | 1 | 20 | 200
(1 row)
-- UPDATE fully surrounding
UPDATE pricing__for_portion_of_quantities SET min_quantity = 0, max_quantity = 100, price = 100;
TABLE pricing ORDER BY min_quantity;
id1 | id2 | id3 | product | min_quantity | max_quantity | price
-----+-----+-----+---------+--------------+--------------+-------
1 | 1 | 1 | Trinket | 1 | 20 | 100
(1 row)
-- UPDATE portion
UPDATE pricing__for_portion_of_quantities SET min_quantity = 10, max_quantity = 20, price = 80;
TABLE pricing ORDER BY min_quantity;
id1 | id2 | id3 | product | min_quantity | max_quantity | price
-----+-----+-----+---------+--------------+--------------+-------
2 | 2 | 2 | Trinket | 1 | 10 | 100
1 | 1 | 1 | Trinket | 10 | 20 | 80
(2 rows)
-- UPDATE portion of multiple rows
UPDATE pricing__for_portion_of_quantities SET min_quantity = 5, max_quantity = 15, price = 90;
TABLE pricing ORDER BY min_quantity;
id1 | id2 | id3 | product | min_quantity | max_quantity | price
-----+-----+-----+---------+--------------+--------------+-------
3 | 3 | 3 | Trinket | 1 | 5 | 100
2 | 2 | 2 | Trinket | 5 | 10 | 90
1 | 1 | 1 | Trinket | 10 | 15 | 90
4 | 4 | 4 | Trinket | 15 | 20 | 80
(4 rows)
-- If we drop the period (without CASCADE) then the FOR PORTION views should be
-- dropped, too.
SELECT periods.drop_period('pricing', 'quantities');
drop_period
-------------
t
(1 row)
TABLE periods.for_portion_views;
table_name | period_name | view_name | trigger_name
------------+-------------+-----------+--------------
(0 rows)
-- Add it back to test the drop_for_portion_view function
SELECT periods.add_period('pricing', 'quantities', 'min_quantity', 'max_quantity');
add_period
------------
t
(1 row)
SELECT periods.add_for_portion_view('pricing', 'quantities');
add_for_portion_view
----------------------
t
(1 row)
-- We can't drop the the table without first dropping the FOR PORTION views
-- because Postgres will complain about dependant objects (our views) before we
-- get a chance to clean them up.
DROP TABLE pricing;
ERROR: cannot drop table pricing because other objects depend on it
DETAIL: view pricing__for_portion_of_quantities depends on table pricing
HINT: Use DROP ... CASCADE to drop the dependent objects too.
SELECT periods.drop_for_portion_view('pricing', NULL);
drop_for_portion_view
-----------------------
t
(1 row)
TABLE periods.for_portion_views;
table_name | period_name | view_name | trigger_name
------------+-------------+-----------+--------------
(0 rows)
DROP TABLE pricing;
DROP SEQUENCE pricing_seq;
/* Types without btree must be excluded, too */
-- v10+
CREATE TABLE bt (
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
pt point, -- something without btree
t text, -- something with btree
s integer,
e integer
);
-- pre v10
CREATE TABLE bt (
id serial PRIMARY KEY,
pt point, -- something without btree
t text, -- something with btree
s integer,
e integer
);
ERROR: relation "bt" already exists
SELECT periods.add_period('bt', 'p', 's', 'e');
add_period
------------
t
(1 row)
SELECT periods.add_for_portion_view('bt', 'p');
add_for_portion_view
----------------------
t
(1 row)
INSERT INTO bt (pt, t, s, e) VALUES ('(0, 0)', 'sample', 10, 40);
TABLE bt ORDER BY s, e;
id | pt | t | s | e
----+-------+--------+----+----
1 | (0,0) | sample | 10 | 40
(1 row)
UPDATE bt__for_portion_of_p SET t = 'simple', s = 20, e = 30;
TABLE bt ORDER BY s, e;
id | pt | t | s | e
----+-------+--------+----+----
2 | (0,0) | sample | 10 | 20
1 | (0,0) | simple | 20 | 30
3 | (0,0) | sample | 30 | 40
(3 rows)
SELECT periods.drop_for_portion_view('bt', 'p');
drop_for_portion_view
-----------------------
t
(1 row)
DROP TABLE bt;
|
