1-- suppress CONTEXT so that function OIDs aren't in output
2\set VERBOSITY terse
3
4insert into T_pkey1 values (1, 'key1-1', 'test key');
5insert into T_pkey1 values (1, 'key1-2', 'test key');
6insert into T_pkey1 values (1, 'key1-3', 'test key');
7insert into T_pkey1 values (2, 'key2-1', 'test key');
8insert into T_pkey1 values (2, 'key2-2', 'test key');
9insert into T_pkey1 values (2, 'key2-3', 'test key');
10
11insert into T_pkey2 values (1, 'key1-1', 'test key');
12insert into T_pkey2 values (1, 'key1-2', 'test key');
13insert into T_pkey2 values (1, 'key1-3', 'test key');
14insert into T_pkey2 values (2, 'key2-1', 'test key');
15insert into T_pkey2 values (2, 'key2-2', 'test key');
16insert into T_pkey2 values (2, 'key2-3', 'test key');
17
18select * from T_pkey1;
19
20-- key2 in T_pkey2 should have upper case only
21select * from T_pkey2;
22
23insert into T_pkey1 values (1, 'KEY1-3', 'should work');
24
25-- Due to the upper case translation in trigger this must fail
26insert into T_pkey2 values (1, 'KEY1-3', 'should fail');
27
28insert into T_dta1 values ('trec 1', 1, 'key1-1');
29insert into T_dta1 values ('trec 2', 1, 'key1-2');
30insert into T_dta1 values ('trec 3', 1, 'key1-3');
31
32-- Must fail due to unknown key in T_pkey1
33insert into T_dta1 values ('trec 4', 1, 'key1-4');
34
35insert into T_dta2 values ('trec 1', 1, 'KEY1-1');
36insert into T_dta2 values ('trec 2', 1, 'KEY1-2');
37insert into T_dta2 values ('trec 3', 1, 'KEY1-3');
38
39-- Must fail due to unknown key in T_pkey2
40insert into T_dta2 values ('trec 4', 1, 'KEY1-4');
41
42select * from T_dta1;
43
44select * from T_dta2;
45
46update T_pkey1 set key2 = 'key2-9' where key1 = 2 and key2 = 'key2-1';
47update T_pkey1 set key2 = 'key1-9' where key1 = 1 and key2 = 'key1-1';
48delete from T_pkey1 where key1 = 2 and key2 = 'key2-2';
49delete from T_pkey1 where key1 = 1 and key2 = 'key1-2';
50
51update T_pkey2 set key2 = 'KEY2-9' where key1 = 2 and key2 = 'KEY2-1';
52update T_pkey2 set key2 = 'KEY1-9' where key1 = 1 and key2 = 'KEY1-1';
53delete from T_pkey2 where key1 = 2 and key2 = 'KEY2-2';
54delete from T_pkey2 where key1 = 1 and key2 = 'KEY1-2';
55
56select * from T_pkey1;
57select * from T_pkey2;
58select * from T_dta1;
59select * from T_dta2;
60
61select tcl_avg(key1) from T_pkey1;
62select tcl_sum(key1) from T_pkey1;
63select tcl_avg(key1) from T_pkey2;
64select tcl_sum(key1) from T_pkey2;
65
66-- The following should return NULL instead of 0
67select tcl_avg(key1) from T_pkey1 where key1 = 99;
68select tcl_sum(key1) from T_pkey1 where key1 = 99;
69
70select 1 @< 2;
71select 100 @< 4;
72
73select * from T_pkey1 order by key1 using @<, key2 collate "C";
74select * from T_pkey2 order by key1 using @<, key2 collate "C";
75
76-- show dump of trigger data
77insert into trigger_test values(1,'insert');
78
79insert into trigger_test_view values(2,'insert');
80update trigger_test_view set v = 'update' where i=1;
81delete from trigger_test_view;
82
83update trigger_test set v = 'update', test_skip=true where i = 1;
84update trigger_test set v = 'update' where i = 1;
85delete from trigger_test;
86truncate trigger_test;
87
88-- Test composite-type arguments
89select tcl_composite_arg_ref1(row('tkey', 42, 'ref2'));
90select tcl_composite_arg_ref2(row('tkey', 42, 'ref2'));
91
92-- Test argisnull primitive
93select tcl_argisnull('foo');
94select tcl_argisnull('');
95select tcl_argisnull(null);
96-- should error
97insert into trigger_test(test_argisnull) values(true);
98select trigger_data();
99
100-- Test spi_lastoid primitive
101create temp table t1 (f1 int);
102select tcl_lastoid('t1');
103create temp table t2 (f1 int) with oids;
104select tcl_lastoid('t2') > 0;
105
106-- test some error cases
107create function tcl_error(out a int, out b int) as $$return {$$ language pltcl;
108select tcl_error();
109
110create function bad_record(out a text, out b text) as $$return [list a]$$ language pltcl;
111select bad_record();
112
113create function bad_field(out a text, out b text) as $$return [list a 1 b 2 cow 3]$$ language pltcl;
114select bad_field();
115
116-- test compound return
117select * from tcl_test_cube_squared(5);
118
119-- test SRF
120select * from tcl_test_squared_rows(0,5);
121
122select * from tcl_test_sequence(0,5) as a;
123
124select 1, tcl_test_sequence(0,5);
125
126create function non_srf() returns int as $$return_next 1$$ language pltcl;
127select non_srf();
128
129create function bad_record_srf(out a text, out b text) returns setof record as $$
130return_next [list a]
131$$ language pltcl;
132select bad_record_srf();
133
134create function bad_field_srf(out a text, out b text) returns setof record as $$
135return_next [list a 1 b 2 cow 3]
136$$ language pltcl;
137select bad_field_srf();
138
139-- test quote
140select tcl_eval('quote foo bar');
141select tcl_eval('quote [format %c 39]');
142select tcl_eval('quote [format %c 92]');
143
144-- Test argisnull
145select tcl_eval('argisnull');
146select tcl_eval('argisnull 14');
147select tcl_eval('argisnull abc');
148
149-- Test return_null
150select tcl_eval('return_null 14');
151-- should error
152insert into trigger_test(test_return_null) values(true);
153
154-- Test spi_exec
155select tcl_eval('spi_exec');
156select tcl_eval('spi_exec -count');
157select tcl_eval('spi_exec -array');
158select tcl_eval('spi_exec -count abc');
159select tcl_eval('spi_exec query loop body toomuch');
160select tcl_eval('spi_exec "begin; rollback;"');
161
162-- Test spi_execp
163select tcl_eval('spi_execp');
164select tcl_eval('spi_execp -count');
165select tcl_eval('spi_execp -array');
166select tcl_eval('spi_execp -count abc');
167select tcl_eval('spi_execp -nulls');
168select tcl_eval('spi_execp ""');
169
170-- test spi_prepare
171select tcl_eval('spi_prepare');
172select tcl_eval('spi_prepare a b');
173select tcl_eval('spi_prepare a "b {"');
174select tcl_error_handling_test($tcl$spi_prepare "select moo" []$tcl$);
175
176-- test full error text
177select tcl_error_handling_test($tcl$
178spi_exec "DO $$
179BEGIN
180RAISE 'my message'
181	USING HINT = 'my hint'
182	, DETAIL = 'my detail'
183	, SCHEMA = 'my schema'
184	, TABLE = 'my table'
185	, COLUMN = 'my column'
186	, CONSTRAINT = 'my constraint'
187	, DATATYPE = 'my datatype'
188;
189END$$;"
190$tcl$);
191
192-- verify tcl_error_handling_test() properly reports non-postgres errors
193select tcl_error_handling_test('moo');
194
195-- test elog
196select tcl_eval('elog');
197select tcl_eval('elog foo bar');
198
199-- test forced error
200select tcl_eval('error "forced error"');
201
202-- test loop control in spi_exec[p]
203select tcl_spi_exec(true, 'break');
204select tcl_spi_exec(true, 'continue');
205select tcl_spi_exec(true, 'error');
206select tcl_spi_exec(true, 'return');
207select tcl_spi_exec(false, 'break');
208select tcl_spi_exec(false, 'continue');
209select tcl_spi_exec(false, 'error');
210select tcl_spi_exec(false, 'return');
211
212-- forcibly run the Tcl event loop for awhile, to check that we have not
213-- messed things up too badly by disabling the Tcl notifier subsystem
214select tcl_eval($$
215  unset -nocomplain ::tcl_vwait
216  after 100 {set ::tcl_vwait 1}
217  vwait ::tcl_vwait
218  unset -nocomplain ::tcl_vwait$$);
219
220-- test transition table visibility
221create table transition_table_test (id int, name text);
222insert into transition_table_test values (1, 'a');
223create function transition_table_test_f() returns trigger language pltcl as
224$$
225  spi_exec -array C "SELECT id, name FROM old_table" {
226    elog INFO "old: $C(id) -> $C(name)"
227  }
228  spi_exec -array C "SELECT id, name FROM new_table" {
229    elog INFO "new: $C(id) -> $C(name)"
230  }
231  return OK
232$$;
233CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test
234  REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
235  FOR EACH STATEMENT EXECUTE PROCEDURE transition_table_test_f();
236update transition_table_test set name = 'b';
237drop table transition_table_test;
238drop function transition_table_test_f();
239