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