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