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