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' where i = 1; 84delete from trigger_test; 85 86-- Test composite-type arguments 87select tcl_composite_arg_ref1(row('tkey', 42, 'ref2')); 88select tcl_composite_arg_ref2(row('tkey', 42, 'ref2')); 89 90-- Test argisnull primitive 91select tcl_argisnull('foo'); 92select tcl_argisnull(''); 93select tcl_argisnull(null); 94 95-- Test spi_lastoid primitive 96create temp table t1 (f1 int); 97select tcl_lastoid('t1'); 98create temp table t2 (f1 int) with oids; 99select tcl_lastoid('t2') > 0; 100