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
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
|
QUERY: create table pkeys (pkey1 int4 not null, pkey2 text not null);
QUERY: create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
QUERY: create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
QUERY: create index fkeys_i on fkeys (fkey1, fkey2);
QUERY: create index fkeys2_i on fkeys2 (fkey21, fkey22);
QUERY: create index fkeys2p_i on fkeys2 (pkey23);
QUERY: insert into pkeys values (10, '1');
QUERY: insert into pkeys values (20, '2');
QUERY: insert into pkeys values (30, '3');
QUERY: insert into pkeys values (40, '4');
QUERY: insert into pkeys values (50, '5');
QUERY: insert into pkeys values (60, '6');
QUERY: create unique index pkeys_i on pkeys (pkey1, pkey2);
QUERY: create trigger check_fkeys_pkey_exist
before insert or update on fkeys
for each row
execute procedure
check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
QUERY: create trigger check_fkeys_pkey2_exist
before insert or update on fkeys
for each row
execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23');
QUERY: create trigger check_fkeys2_pkey_exist
before insert or update on fkeys2
for each row
execute procedure
check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
QUERY: create trigger check_pkeys_fkey_cascade
before delete or update on pkeys
for each row
execute procedure
check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
QUERY: create trigger check_fkeys2_fkey_restrict
before delete or update on fkeys2
for each row
execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
QUERY: insert into fkeys2 values (10, '1', 1);
QUERY: insert into fkeys2 values (30, '3', 2);
QUERY: insert into fkeys2 values (40, '4', 5);
QUERY: insert into fkeys2 values (50, '5', 3);
QUERY: insert into fkeys2 values (70, '5', 3);
ERROR: check_fkeys2_pkey_exist: tuple references non-existing key in pkeys
QUERY: insert into fkeys values (10, '1', 2);
QUERY: insert into fkeys values (30, '3', 3);
QUERY: insert into fkeys values (40, '4', 2);
QUERY: insert into fkeys values (50, '5', 2);
QUERY: insert into fkeys values (70, '5', 1);
ERROR: check_fkeys_pkey_exist: tuple references non-existing key in pkeys
QUERY: insert into fkeys values (60, '6', 4);
ERROR: check_fkeys_pkey2_exist: tuple references non-existing key in fkeys2
QUERY: delete from pkeys where pkey1 = 30 and pkey2 = '3';
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
ERROR: check_fkeys2_fkey_restrict: tuple referenced in fkeys
QUERY: delete from pkeys where pkey1 = 40 and pkey2 = '4';
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
QUERY: update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
ERROR: check_fkeys2_fkey_restrict: tuple referenced in fkeys
QUERY: update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
QUERY: DROP TABLE pkeys;
QUERY: DROP TABLE fkeys;
QUERY: DROP TABLE fkeys2;
QUERY: create table dup17 (x int4);
QUERY: create trigger dup17_before
before insert on dup17
for each row
execute procedure
funny_dup17 ()
;
QUERY: insert into dup17 values (17);
NOTICE: funny_dup17 (fired BEFORE) on level 1: 0/0 tuples inserted/selected
QUERY: select count(*) from dup17;
count
-----
1
(1 row)
QUERY: insert into dup17 values (17);
NOTICE: funny_dup17 (fired BEFORE) on level 17: 1/2 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 16: 1/3 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 15: 1/4 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 14: 1/5 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 13: 1/6 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 12: 1/7 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 11: 1/8 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 10: 1/9 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 9: 1/10 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 8: 1/11 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 7: 1/12 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 6: 1/13 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 5: 1/14 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 4: 1/15 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 3: 1/16 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 2: 1/17 tuples inserted/selected
NOTICE: funny_dup17 (fired BEFORE) on level 1: 1/18 tuples inserted/selected
QUERY: select count(*) from dup17;
count
-----
19
(1 row)
QUERY: drop trigger dup17_before on dup17;
QUERY: create trigger dup17_after
after insert on dup17
for each row
execute procedure
funny_dup17 ()
;
QUERY: insert into dup17 values (13);
NOTICE: funny_dup17 (fired AFTER ) on level 17: 17/34 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 16: 16/49 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 15: 15/63 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 14: 14/76 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 13: 13/88 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 12: 12/99 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 11: 11/109 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 10: 10/118 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 9: 9/126 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 8: 8/133 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 7: 7/139 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 6: 6/144 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 5: 5/148 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 4: 4/151 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 3: 3/153 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 2: 2/154 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 1: 1/154 tuples inserted/selected
QUERY: select count(*) from dup17 where x = 13;
count
-----
154
(1 row)
QUERY: insert into dup17 values (13);
NOTICE: funny_dup17 (fired AFTER ) on level 17: 171/342 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 16: 170/511 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 15: 169/679 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 14: 168/846 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 13: 167/1012 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 12: 166/1177 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 11: 165/1341 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 10: 164/1504 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 9: 163/1666 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 8: 162/1827 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 7: 161/1987 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 6: 160/2146 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 5: 159/2304 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 4: 158/2461 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 3: 157/2617 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 2: 156/2772 tuples inserted/selected
NOTICE: funny_dup17 (fired AFTER ) on level 1: 155/2926 tuples inserted/selected
QUERY: select count(*) from dup17 where x = 13;
count
-----
2926
(1 row)
QUERY: DROP TABLE dup17;
QUERY: create sequence ttdummy_seq increment 10 start 0 minvalue 0;
QUERY: create table tttest (
price_id int4,
price_val int4,
price_on int4,
price_off int4 default 999999
);
QUERY: create trigger ttdummy
before delete or update on tttest
for each row
execute procedure
ttdummy (price_on, price_off);
QUERY: create trigger ttserial
before insert or update on tttest
for each row
execute procedure
autoinc (price_on, ttdummy_seq);
QUERY: insert into tttest values (1, 1, null);
QUERY: insert into tttest values (2, 2, null);
QUERY: insert into tttest values (3, 3, 0);
QUERY: select * from tttest;
price_id|price_val|price_on|price_off
--------+---------+--------+---------
1| 1| 10| 999999
2| 2| 20| 999999
3| 3| 30| 999999
(3 rows)
QUERY: delete from tttest where price_id = 2;
QUERY: select * from tttest;
price_id|price_val|price_on|price_off
--------+---------+--------+---------
1| 1| 10| 999999
3| 3| 30| 999999
2| 2| 20| 40
(3 rows)
QUERY: select * from tttest where price_off = 999999;
price_id|price_val|price_on|price_off
--------+---------+--------+---------
1| 1| 10| 999999
3| 3| 30| 999999
(2 rows)
QUERY: update tttest set price_val = 30 where price_id = 3;
QUERY: select * from tttest;
price_id|price_val|price_on|price_off
--------+---------+--------+---------
1| 1| 10| 999999
2| 2| 20| 40
3| 30| 50| 999999
3| 3| 30| 50
(4 rows)
QUERY: update tttest set price_id = 5 where price_id = 3;
QUERY: select * from tttest;
price_id|price_val|price_on|price_off
--------+---------+--------+---------
1| 1| 10| 999999
2| 2| 20| 40
3| 3| 30| 50
5| 30| 60| 999999
3| 30| 50| 60
(5 rows)
QUERY: select set_ttdummy(0);
set_ttdummy
-----------
1
(1 row)
QUERY: delete from tttest where price_id = 5;
QUERY: update tttest set price_off = 999999 where price_val = 30;
QUERY: select * from tttest;
price_id|price_val|price_on|price_off
--------+---------+--------+---------
1| 1| 10| 999999
2| 2| 20| 40
3| 3| 30| 50
3| 30| 50| 999999
(4 rows)
QUERY: update tttest set price_id = 5 where price_id = 3;
QUERY: select * from tttest;
price_id|price_val|price_on|price_off
--------+---------+--------+---------
1| 1| 10| 999999
2| 2| 20| 40
5| 3| 30| 50
5| 30| 50| 999999
(4 rows)
QUERY: select set_ttdummy(1);
set_ttdummy
-----------
0
(1 row)
QUERY: update tttest set price_on = -1 where price_id = 1;
ERROR: ttdummy (tttest): you can't change price_on and/or price_off columns (use set_ttdummy)
QUERY: select set_ttdummy(0);
set_ttdummy
-----------
1
(1 row)
QUERY: update tttest set price_on = -1 where price_id = 1;
QUERY: select * from tttest;
price_id|price_val|price_on|price_off
--------+---------+--------+---------
2| 2| 20| 40
5| 3| 30| 50
5| 30| 50| 999999
1| 1| -1| 999999
(4 rows)
QUERY: select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5;
price_id|price_val|price_on|price_off
--------+---------+--------+---------
5| 3| 30| 50
(1 row)
QUERY: drop table tttest;
QUERY: drop sequence ttdummy_seq;
|
