| blob | 6201a9bcec320ac909dc136a385f649e636691a0 |
1 -- suppress CONTEXT so that function OIDs aren't in output
2 \set VERBOSITY terse
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;
16 key1 | key2 | txt
17 ------+----------------------+------------------------------------------
18 1 | key1-1 | test key
19 1 | key1-2 | test key
20 1 | key1-3 | test key
21 2 | key2-1 | test key
22 2 | key2-2 | test key
23 2 | key2-3 | test key
24 (6 rows)
26 -- key2 in T_pkey2 should have upper case only
27 select * from T_pkey2;
28 key1 | key2 | txt
29 ------+----------------------+------------------------------------------
30 1 | KEY1-1 | test key
31 1 | KEY1-2 | test key
32 1 | KEY1-3 | test key
33 2 | KEY2-1 | test key
34 2 | KEY2-2 | test key
35 2 | KEY2-3 | test key
36 (6 rows)
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
54 select * from T_dta1;
55 tkey | ref1 | ref2
56 ------------+------+----------------------
57 trec 1 | 1 | key1-1
58 trec 2 | 1 | key1-2
59 trec 3 | 1 | key1-3
60 (3 rows)
62 select * from T_dta2;
63 tkey | ref1 | ref2
64 ------------+------+----------------------
65 trec 1 | 1 | KEY1-1
66 trec 2 | 1 | KEY1-2
67 trec 3 | 1 | KEY1-3
68 (3 rows)
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;
83 key1 | key2 | txt
84 ------+----------------------+------------------------------------------
85 1 | key1-1 | test key
86 1 | key1-2 | test key
87 1 | key1-3 | test key
88 2 | key2-3 | test key
89 1 | KEY1-3 | should work
90 2 | key2-9 | test key
91 (6 rows)
93 select * from T_pkey2;
94 key1 | key2 | txt
95 ------+----------------------+------------------------------------------
96 1 | KEY1-3 | test key
97 2 | KEY2-3 | test key
98 2 | KEY2-9 | test key
99 1 | KEY1-9 | test key
100 (4 rows)
102 select * from T_dta1;
103 tkey | ref1 | ref2
104 ------------+------+----------------------
105 trec 1 | 1 | key1-1
106 trec 2 | 1 | key1-2
107 trec 3 | 1 | key1-3
108 (3 rows)
110 select * from T_dta2;
111 tkey | ref1 | ref2
112 ------------+------+----------------------
113 trec 3 | 1 | KEY1-3
114 trec 1 | 1 | KEY1-9
115 (2 rows)
117 select tcl_avg(key1) from T_pkey1;
118 tcl_avg
119 ---------
120 1
121 (1 row)
123 select tcl_sum(key1) from T_pkey1;
124 tcl_sum
125 ---------
126 8
127 (1 row)
129 select tcl_avg(key1) from T_pkey2;
130 tcl_avg
131 ---------
132 1
133 (1 row)
135 select tcl_sum(key1) from T_pkey2;
136 tcl_sum
137 ---------
138 6
139 (1 row)
141 -- The following should return NULL instead of 0
142 select tcl_avg(key1) from T_pkey1 where key1 = 99;
143 tcl_avg
144 ---------
146 (1 row)
148 select tcl_sum(key1) from T_pkey1 where key1 = 99;
149 tcl_sum
150 ---------
151 0
152 (1 row)
154 select 1 @< 2;
155 ?column?
156 ----------
157 t
158 (1 row)
160 select 100 @< 4;
161 ?column?
162 ----------
163 f
164 (1 row)
166 select * from T_pkey1 order by key1 using @<, key2;
167 key1 | key2 | txt
168 ------+----------------------+------------------------------------------
169 1 | key1-1 | test key
170 1 | key1-2 | test key
171 1 | key1-3 | test key
172 1 | KEY1-3 | should work
173 2 | key2-3 | test key
174 2 | key2-9 | test key
175 (6 rows)
177 select * from T_pkey2 order by key1 using @<, key2;
178 key1 | key2 | txt
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
184 (4 rows)
186 -- show dump of trigger data
187 insert into trigger_test values(1,'insert');
188 NOTICE: NEW: {i: 1, v: insert}
189 NOTICE: OLD: {}
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 update trigger_test set v = 'update' where i = 1;
200 NOTICE: NEW: {i: 1, v: update}
201 NOTICE: OLD: {i: 1, v: insert}
202 NOTICE: TG_level: ROW
203 NOTICE: TG_name: show_trigger_data_trig
204 NOTICE: TG_op: UPDATE
205 NOTICE: TG_relatts: {{} i v}
206 NOTICE: TG_relid: bogus:12345
207 NOTICE: TG_table_name: trigger_test
208 NOTICE: TG_table_schema: public
209 NOTICE: TG_when: BEFORE
210 NOTICE: args: {23 skidoo}
211 delete from trigger_test;
212 NOTICE: NEW: {}
213 NOTICE: OLD: {i: 1, v: update}
214 NOTICE: TG_level: ROW
215 NOTICE: TG_name: show_trigger_data_trig
216 NOTICE: TG_op: DELETE
217 NOTICE: TG_relatts: {{} i v}
218 NOTICE: TG_relid: bogus:12345
219 NOTICE: TG_table_name: trigger_test
220 NOTICE: TG_table_schema: public
221 NOTICE: TG_when: BEFORE
222 NOTICE: args: {23 skidoo}