1
-- suppress CONTEXT so that function OIDs aren't in output
3
insert into T_pkey1 values (1, 'key1-1', 'test key');
4
insert into T_pkey1 values (1, 'key1-2', 'test key');
5
insert into T_pkey1 values (1, 'key1-3', 'test key');
6
insert into T_pkey1 values (2, 'key2-1', 'test key');
7
insert into T_pkey1 values (2, 'key2-2', 'test key');
8
insert into T_pkey1 values (2, 'key2-3', 'test key');
9
insert into T_pkey2 values (1, 'key1-1', 'test key');
10
insert into T_pkey2 values (1, 'key1-2', 'test key');
11
insert into T_pkey2 values (1, 'key1-3', 'test key');
12
insert into T_pkey2 values (2, 'key2-1', 'test key');
13
insert into T_pkey2 values (2, 'key2-2', 'test key');
14
insert into T_pkey2 values (2, 'key2-3', 'test key');
15
select * from T_pkey1;
17
------+----------------------+------------------------------------------
26
-- key2 in T_pkey2 should have upper case only
27
select * from T_pkey2;
29
------+----------------------+------------------------------------------
38
insert into T_pkey1 values (1, 'KEY1-3', 'should work');
39
-- Due to the upper case translation in trigger this must fail
40
insert into T_pkey2 values (1, 'KEY1-3', 'should fail');
41
ERROR: duplicate key '1', 'KEY1-3' for T_pkey2
42
insert into T_dta1 values ('trec 1', 1, 'key1-1');
43
insert into T_dta1 values ('trec 2', 1, 'key1-2');
44
insert into T_dta1 values ('trec 3', 1, 'key1-3');
45
-- Must fail due to unknown key in T_pkey1
46
insert into T_dta1 values ('trec 4', 1, 'key1-4');
47
ERROR: key for t_dta1 not in t_pkey1
48
insert into T_dta2 values ('trec 1', 1, 'KEY1-1');
49
insert into T_dta2 values ('trec 2', 1, 'KEY1-2');
50
insert into T_dta2 values ('trec 3', 1, 'KEY1-3');
51
-- Must fail due to unknown key in T_pkey2
52
insert into T_dta2 values ('trec 4', 1, 'KEY1-4');
53
ERROR: key for t_dta2 not in t_pkey2
56
------------+------+----------------------
64
------------+------+----------------------
70
update T_pkey1 set key2 = 'key2-9' where key1 = 2 and key2 = 'key2-1';
71
update T_pkey1 set key2 = 'key1-9' where key1 = 1 and key2 = 'key1-1';
72
ERROR: key '1', 'key1-1 ' referenced by T_dta1
73
delete from T_pkey1 where key1 = 2 and key2 = 'key2-2';
74
delete from T_pkey1 where key1 = 1 and key2 = 'key1-2';
75
ERROR: key '1', 'key1-2 ' referenced by T_dta1
76
update T_pkey2 set key2 = 'KEY2-9' where key1 = 2 and key2 = 'KEY2-1';
77
update T_pkey2 set key2 = 'KEY1-9' where key1 = 1 and key2 = 'KEY1-1';
78
NOTICE: updated 1 entries in T_dta2 for new key in T_pkey2
79
delete from T_pkey2 where key1 = 2 and key2 = 'KEY2-2';
80
delete from T_pkey2 where key1 = 1 and key2 = 'KEY1-2';
81
NOTICE: deleted 1 entries from T_dta2
82
select * from T_pkey1;
84
------+----------------------+------------------------------------------
89
1 | KEY1-3 | should work
93
select * from T_pkey2;
95
------+----------------------+------------------------------------------
102
select * from T_dta1;
104
------------+------+----------------------
110
select * from T_dta2;
112
------------+------+----------------------
117
select tcl_avg(key1) from T_pkey1;
123
select tcl_sum(key1) from T_pkey1;
129
select tcl_avg(key1) from T_pkey2;
135
select tcl_sum(key1) from T_pkey2;
141
-- The following should return NULL instead of 0
142
select tcl_avg(key1) from T_pkey1 where key1 = 99;
148
select tcl_sum(key1) from T_pkey1 where key1 = 99;
166
select * from T_pkey1 order by key1 using @<, key2;
168
------+----------------------+------------------------------------------
169
1 | KEY1-3 | should work
170
1 | key1-1 | test key
171
1 | key1-2 | test key
172
1 | key1-3 | test key
173
2 | key2-3 | test key
174
2 | key2-9 | test key
177
select * from T_pkey2 order by key1 using @<, key2;
179
------+----------------------+------------------------------------------
180
1 | KEY1-3 | test key
181
1 | KEY1-9 | test key
182
2 | KEY2-3 | test key
183
2 | KEY2-9 | test key
186
-- show dump of trigger data
187
insert into trigger_test values(1,'insert');
188
NOTICE: NEW: {i: 1, v: insert}
190
NOTICE: TG_level: ROW
191
NOTICE: TG_name: show_trigger_data_trig
192
NOTICE: TG_op: INSERT
193
NOTICE: TG_relatts: {{} i v}
194
NOTICE: TG_relid: bogus:12345
195
NOTICE: TG_table_name: trigger_test
196
NOTICE: TG_table_schema: public
197
NOTICE: TG_when: BEFORE
198
NOTICE: args: {23 skidoo}
199
insert into trigger_test_view values(2,'insert');
200
NOTICE: NEW: {i: 2, v: insert}
202
NOTICE: TG_level: ROW
203
NOTICE: TG_name: show_trigger_data_view_trig
204
NOTICE: TG_op: INSERT
205
NOTICE: TG_relatts: {{} i v}
206
NOTICE: TG_relid: bogus:12345
207
NOTICE: TG_table_name: trigger_test_view
208
NOTICE: TG_table_schema: public
209
NOTICE: TG_when: {INSTEAD OF}
210
NOTICE: args: {24 {skidoo view}}
211
update trigger_test_view set v = 'update' where i=1;
212
NOTICE: NEW: {i: 1, v: update}
213
NOTICE: OLD: {i: 1, v: insert}
214
NOTICE: TG_level: ROW
215
NOTICE: TG_name: show_trigger_data_view_trig
216
NOTICE: TG_op: UPDATE
217
NOTICE: TG_relatts: {{} i v}
218
NOTICE: TG_relid: bogus:12345
219
NOTICE: TG_table_name: trigger_test_view
220
NOTICE: TG_table_schema: public
221
NOTICE: TG_when: {INSTEAD OF}
222
NOTICE: args: {24 {skidoo view}}
223
delete from trigger_test_view;
225
NOTICE: OLD: {i: 1, v: insert}
226
NOTICE: TG_level: ROW
227
NOTICE: TG_name: show_trigger_data_view_trig
228
NOTICE: TG_op: DELETE
229
NOTICE: TG_relatts: {{} i v}
230
NOTICE: TG_relid: bogus:12345
231
NOTICE: TG_table_name: trigger_test_view
232
NOTICE: TG_table_schema: public
233
NOTICE: TG_when: {INSTEAD OF}
234
NOTICE: args: {24 {skidoo view}}
235
update trigger_test set v = 'update' where i = 1;
236
NOTICE: NEW: {i: 1, v: update}
237
NOTICE: OLD: {i: 1, v: insert}
238
NOTICE: TG_level: ROW
239
NOTICE: TG_name: show_trigger_data_trig
240
NOTICE: TG_op: UPDATE
241
NOTICE: TG_relatts: {{} i v}
242
NOTICE: TG_relid: bogus:12345
243
NOTICE: TG_table_name: trigger_test
244
NOTICE: TG_table_schema: public
245
NOTICE: TG_when: BEFORE
246
NOTICE: args: {23 skidoo}
247
delete from trigger_test;
249
NOTICE: OLD: {i: 1, v: update}
250
NOTICE: TG_level: ROW
251
NOTICE: TG_name: show_trigger_data_trig
252
NOTICE: TG_op: DELETE
253
NOTICE: TG_relatts: {{} i v}
254
NOTICE: TG_relid: bogus:12345
255
NOTICE: TG_table_name: trigger_test
256
NOTICE: TG_table_schema: public
257
NOTICE: TG_when: BEFORE
258
NOTICE: args: {23 skidoo}