1 -- suppress CONTEXT so that function OIDs aren't in output
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;
17 ------+----------------------+------------------------------------------
26 -- key2 in T_pkey2 should have upper case only
27 select * from T_pkey2;
29 ------+----------------------+------------------------------------------
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
56 ------------+------+----------------------
64 ------------+------+----------------------
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;
84 ------+----------------------+------------------------------------------
89 1 | KEY1-3 | should work
93 select * from T_pkey2;
95 ------+----------------------+------------------------------------------
102 select * from T_dta1;
104 ------------+------+----------------------
110 select * from T_dta2;
112 ------------+------+----------------------
117 select tcl_avg(key1) from T_pkey1;
123 select tcl_sum(key1) from T_pkey1;
129 select tcl_avg(key1) from T_pkey2;
135 select tcl_sum(key1) from T_pkey2;
141 -- The following should return NULL instead of 0
142 select tcl_avg(key1) from T_pkey1 where key1 = 99;
148 select tcl_sum(key1) from T_pkey1 where key1 = 99;
166 select * from T_pkey1 order by key1 using @<, key2 collate "C";
168 ------+----------------------+------------------------------------------
169 1 | KEY1-3 | should work
170 1 | key1-1 | test key
171 1 | key1-2 | test key
172 1 | key1-3 | test key
173 2 | key2-3 | test key
174 2 | key2-9 | test key
177 select * from T_pkey2 order by key1 using @<, key2 collate "C";
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
186 -- show dump of trigger data
187 insert into trigger_test values(1,'insert');
190 NOTICE: TG_level: STATEMENT
191 NOTICE: TG_name: statement_trigger
192 NOTICE: TG_op: INSERT
193 NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
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: {42 {statement trigger}}
199 NOTICE: NEW: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: insert}
201 NOTICE: TG_level: ROW
202 NOTICE: TG_name: show_trigger_data_trig
203 NOTICE: TG_op: INSERT
204 NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
205 NOTICE: TG_relid: bogus:12345
206 NOTICE: TG_table_name: trigger_test
207 NOTICE: TG_table_schema: public
208 NOTICE: TG_when: BEFORE
209 NOTICE: args: {23 skidoo}
210 insert into trigger_test_view values(2,'insert');
211 NOTICE: NEW: {i: 2, v: insert}
213 NOTICE: TG_level: ROW
214 NOTICE: TG_name: show_trigger_data_view_trig
215 NOTICE: TG_op: INSERT
216 NOTICE: TG_relatts: {{} i v}
217 NOTICE: TG_relid: bogus:12345
218 NOTICE: TG_table_name: trigger_test_view
219 NOTICE: TG_table_schema: public
220 NOTICE: TG_when: {INSTEAD OF}
221 NOTICE: args: {24 {skidoo view}}
222 update trigger_test_view set v = 'update' where i=1;
223 NOTICE: NEW: {i: 1, v: update}
224 NOTICE: OLD: {i: 1, v: insert}
225 NOTICE: TG_level: ROW
226 NOTICE: TG_name: show_trigger_data_view_trig
227 NOTICE: TG_op: UPDATE
228 NOTICE: TG_relatts: {{} i v}
229 NOTICE: TG_relid: bogus:12345
230 NOTICE: TG_table_name: trigger_test_view
231 NOTICE: TG_table_schema: public
232 NOTICE: TG_when: {INSTEAD OF}
233 NOTICE: args: {24 {skidoo view}}
234 delete from trigger_test_view;
236 NOTICE: OLD: {i: 1, v: insert}
237 NOTICE: TG_level: ROW
238 NOTICE: TG_name: show_trigger_data_view_trig
239 NOTICE: TG_op: DELETE
240 NOTICE: TG_relatts: {{} i v}
241 NOTICE: TG_relid: bogus:12345
242 NOTICE: TG_table_name: trigger_test_view
243 NOTICE: TG_table_schema: public
244 NOTICE: TG_when: {INSTEAD OF}
245 NOTICE: args: {24 {skidoo view}}
246 update trigger_test set v = 'update', test_skip=true where i = 1;
249 NOTICE: TG_level: STATEMENT
250 NOTICE: TG_name: statement_trigger
251 NOTICE: TG_op: UPDATE
252 NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
253 NOTICE: TG_relid: bogus:12345
254 NOTICE: TG_table_name: trigger_test
255 NOTICE: TG_table_schema: public
256 NOTICE: TG_when: BEFORE
257 NOTICE: args: {42 {statement trigger}}
258 NOTICE: SKIPPING OPERATION UPDATE
259 update trigger_test set v = 'update' where i = 1;
262 NOTICE: TG_level: STATEMENT
263 NOTICE: TG_name: statement_trigger
264 NOTICE: TG_op: UPDATE
265 NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
266 NOTICE: TG_relid: bogus:12345
267 NOTICE: TG_table_name: trigger_test
268 NOTICE: TG_table_schema: public
269 NOTICE: TG_when: BEFORE
270 NOTICE: args: {42 {statement trigger}}
271 NOTICE: NEW: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: update}
272 NOTICE: OLD: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: insert}
273 NOTICE: TG_level: ROW
274 NOTICE: TG_name: show_trigger_data_trig
275 NOTICE: TG_op: UPDATE
276 NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
277 NOTICE: TG_relid: bogus:12345
278 NOTICE: TG_table_name: trigger_test
279 NOTICE: TG_table_schema: public
280 NOTICE: TG_when: BEFORE
281 NOTICE: args: {23 skidoo}
282 delete from trigger_test;
285 NOTICE: TG_level: STATEMENT
286 NOTICE: TG_name: statement_trigger
287 NOTICE: TG_op: DELETE
288 NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
289 NOTICE: TG_relid: bogus:12345
290 NOTICE: TG_table_name: trigger_test
291 NOTICE: TG_table_schema: public
292 NOTICE: TG_when: BEFORE
293 NOTICE: args: {42 {statement trigger}}
295 NOTICE: OLD: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: update}
296 NOTICE: TG_level: ROW
297 NOTICE: TG_name: show_trigger_data_trig
298 NOTICE: TG_op: DELETE
299 NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
300 NOTICE: TG_relid: bogus:12345
301 NOTICE: TG_table_name: trigger_test
302 NOTICE: TG_table_schema: public
303 NOTICE: TG_when: BEFORE
304 NOTICE: args: {23 skidoo}
305 truncate trigger_test;
308 NOTICE: TG_level: STATEMENT
309 NOTICE: TG_name: statement_trigger
310 NOTICE: TG_op: TRUNCATE
311 NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
312 NOTICE: TG_relid: bogus:12345
313 NOTICE: TG_table_name: trigger_test
314 NOTICE: TG_table_schema: public
315 NOTICE: TG_when: BEFORE
316 NOTICE: args: {42 {statement trigger}}
317 -- Test composite-type arguments
318 select tcl_composite_arg_ref1(row('tkey', 42, 'ref2'));
319 tcl_composite_arg_ref1
320 ------------------------
324 select tcl_composite_arg_ref2(row('tkey', 42, 'ref2'));
325 tcl_composite_arg_ref2
326 ------------------------
330 -- More tests for composite argument/result types
331 create domain d_dta1 as T_dta1 check ((value).ref1 > 0);
332 create function tcl_record_arg(record, fldname text) returns int as '
335 select tcl_record_arg(row('tkey', 42, 'ref2')::T_dta1, 'ref1');
341 select tcl_record_arg(row('tkey', 42, 'ref2')::d_dta1, 'ref1');
347 select tcl_record_arg(row(2,4), 'f2');
353 create function tcl_cdomain_arg(d_dta1) returns int as '
356 select tcl_cdomain_arg(row('tkey', 42, 'ref2'));
362 select tcl_cdomain_arg(row('tkey', 42, 'ref2')::T_dta1);
368 select tcl_cdomain_arg(row('tkey', -1, 'ref2')); -- fail
369 ERROR: value for domain d_dta1 violates check constraint "d_dta1_check"
370 -- Test argisnull primitive
371 select tcl_argisnull('foo');
377 select tcl_argisnull('');
383 select tcl_argisnull(null);
390 insert into trigger_test(test_argisnull) values(true);
393 NOTICE: TG_level: STATEMENT
394 NOTICE: TG_name: statement_trigger
395 NOTICE: TG_op: INSERT
396 NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
397 NOTICE: TG_relid: bogus:12345
398 NOTICE: TG_table_name: trigger_test
399 NOTICE: TG_table_schema: public
400 NOTICE: TG_when: BEFORE
401 NOTICE: args: {42 {statement trigger}}
402 ERROR: argisnull cannot be used in triggers
403 select trigger_data();
404 ERROR: trigger functions can only be called as triggers
405 -- test some error cases
406 create function tcl_error(out a int, out b int) as $$return {$$ language pltcl;
408 ERROR: missing close-brace
409 create function bad_record(out a text, out b text) as $$return [list a]$$ language pltcl;
411 ERROR: column name/value list must have even number of elements
412 create function bad_field(out a text, out b text) as $$return [list a 1 b 2 cow 3]$$ language pltcl;
414 ERROR: column name/value list contains nonexistent column name "cow"
415 -- test compound return
416 select * from tcl_test_cube_squared(5);
423 select * from tcl_test_squared_rows(0,5);
433 select * from tcl_test_sequence(0,5) as a;
443 select 1, tcl_test_sequence(0,5);
444 ?column? | tcl_test_sequence
445 ----------+-------------------
453 create function non_srf() returns int as $$return_next 1$$ language pltcl;
455 ERROR: return_next cannot be used in non-set-returning functions
456 create function bad_record_srf(out a text, out b text) returns setof record as $$
459 select bad_record_srf();
460 ERROR: column name/value list must have even number of elements
461 create function bad_field_srf(out a text, out b text) returns setof record as $$
462 return_next [list a 1 b 2 cow 3]
464 select bad_field_srf();
465 ERROR: column name/value list contains nonexistent column name "cow"
466 -- test composite and domain-over-composite results
467 create function tcl_composite_result(int) returns T_dta1 as $$
468 return [list tkey tkey1 ref1 $1 ref2 ref22]
470 select tcl_composite_result(1001);
472 --------------------------------------------
473 ("tkey1 ",1001,"ref22 ")
476 select * from tcl_composite_result(1002);
478 ------------+------+----------------------
482 create function tcl_dcomposite_result(int) returns d_dta1 as $$
483 return [list tkey tkey2 ref1 $1 ref2 ref42]
485 select tcl_dcomposite_result(1001);
486 tcl_dcomposite_result
487 --------------------------------------------
488 ("tkey2 ",1001,"ref42 ")
491 select * from tcl_dcomposite_result(1002);
493 ------------+------+----------------------
497 select * from tcl_dcomposite_result(-1); -- fail
498 ERROR: value for domain d_dta1 violates check constraint "d_dta1_check"
499 create function tcl_record_result(int) returns record as $$
500 return [list q1 sometext q2 $1 q3 moretext]
502 select tcl_record_result(42); -- fail
503 ERROR: function returning record called in context that cannot accept type record
504 select * from tcl_record_result(42); -- fail
505 ERROR: a column definition list is required for functions returning "record" at character 15
506 select * from tcl_record_result(42) as (q1 text, q2 int, q3 text);
508 ----------+----+----------
509 sometext | 42 | moretext
512 select * from tcl_record_result(42) as (q1 text, q2 int, q3 text, q4 int);
514 ----------+----+----------+----
515 sometext | 42 | moretext |
518 select * from tcl_record_result(42) as (q1 text, q2 int, q4 int); -- fail
519 ERROR: column name/value list contains nonexistent column name "q3"
521 select tcl_eval('quote foo bar');
522 ERROR: wrong # args: should be "quote string"
523 select tcl_eval('quote [format %c 39]');
529 select tcl_eval('quote [format %c 92]');
536 select tcl_eval('argisnull');
537 ERROR: wrong # args: should be "argisnull argno"
538 select tcl_eval('argisnull 14');
539 ERROR: argno out of range
540 select tcl_eval('argisnull abc');
541 ERROR: expected integer but got "abc"
543 select tcl_eval('return_null 14');
544 ERROR: wrong # args: should be "return_null "
546 insert into trigger_test(test_return_null) values(true);
549 NOTICE: TG_level: STATEMENT
550 NOTICE: TG_name: statement_trigger
551 NOTICE: TG_op: INSERT
552 NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
553 NOTICE: TG_relid: bogus:12345
554 NOTICE: TG_table_name: trigger_test
555 NOTICE: TG_table_schema: public
556 NOTICE: TG_when: BEFORE
557 NOTICE: args: {42 {statement trigger}}
558 ERROR: return_null cannot be used in triggers
560 select tcl_eval('spi_exec');
561 ERROR: wrong # args: should be "spi_exec ?-count n? ?-array name? query ?loop body?"
562 select tcl_eval('spi_exec -count');
563 ERROR: missing argument to -count or -array
564 select tcl_eval('spi_exec -array');
565 ERROR: missing argument to -count or -array
566 select tcl_eval('spi_exec -count abc');
567 ERROR: expected integer but got "abc"
568 select tcl_eval('spi_exec query loop body toomuch');
569 ERROR: wrong # args: should be "query ?loop body?"
570 select tcl_eval('spi_exec "begin; rollback;"');
571 ERROR: pltcl: SPI_execute failed: SPI_ERROR_TRANSACTION
573 select tcl_eval('spi_execp');
574 ERROR: missing argument to -count or -array
575 select tcl_eval('spi_execp -count');
576 ERROR: missing argument to -array, -count or -nulls
577 select tcl_eval('spi_execp -array');
578 ERROR: missing argument to -array, -count or -nulls
579 select tcl_eval('spi_execp -count abc');
580 ERROR: expected integer but got "abc"
581 select tcl_eval('spi_execp -nulls');
582 ERROR: missing argument to -array, -count or -nulls
583 select tcl_eval('spi_execp ""');
584 ERROR: invalid queryid ''
586 select tcl_eval('spi_prepare');
587 ERROR: wrong # args: should be "spi_prepare query argtypes"
588 select tcl_eval('spi_prepare a b');
589 ERROR: type "b" does not exist
590 select tcl_eval('spi_prepare a "b {"');
591 ERROR: unmatched open brace in list
592 select tcl_error_handling_test($tcl$spi_prepare "select moo" []$tcl$);
593 tcl_error_handling_test
594 --------------------------------------
596 condition: undefined_column +
598 message: column "moo" does not exist+
599 statement: select moo
602 -- test full error text
603 select tcl_error_handling_test($tcl$
607 USING HINT = 'my hint'
608 , DETAIL = 'my detail'
609 , SCHEMA = 'my schema'
611 , COLUMN = 'my column'
612 , CONSTRAINT = 'my constraint'
613 , DATATYPE = 'my datatype'
617 tcl_error_handling_test
618 --------------------------------------------------------------
621 condition: raise_exception +
622 constraint: my constraint +
623 context: PL/pgSQL function inline_code_block line 3 at RAISE+
624 SQL statement "DO $$ +
627 USING HINT = 'my hint' +
628 , DETAIL = 'my detail' +
629 , SCHEMA = 'my schema' +
630 , TABLE = 'my table' +
631 , COLUMN = 'my column' +
632 , CONSTRAINT = 'my constraint' +
633 , DATATYPE = 'my datatype' +
636 datatype: my datatype +
639 message: my message +
644 -- verify tcl_error_handling_test() properly reports non-postgres errors
645 select tcl_error_handling_test('moo');
646 tcl_error_handling_test
647 ----------------------------
648 invalid command name "moo"
652 select tcl_eval('elog');
653 ERROR: wrong # args: should be "elog level msg"
654 select tcl_eval('elog foo bar');
655 ERROR: bad priority "foo": must be DEBUG, LOG, INFO, NOTICE, WARNING, ERROR, or FATAL
657 select tcl_eval('error "forced error"');
659 -- test loop control in spi_exec[p]
660 select tcl_spi_exec(true, 'break');
661 NOTICE: col1 1, col2 foo
662 NOTICE: col1 2, col2 bar
663 NOTICE: action: break
664 NOTICE: end of function
670 select tcl_spi_exec(true, 'continue');
671 NOTICE: col1 1, col2 foo
672 NOTICE: col1 2, col2 bar
673 NOTICE: action: continue
674 NOTICE: col1 3, col2 baz
675 NOTICE: end of function
681 select tcl_spi_exec(true, 'error');
682 NOTICE: col1 1, col2 foo
683 NOTICE: col1 2, col2 bar
684 NOTICE: action: error
686 select tcl_spi_exec(true, 'return');
687 NOTICE: col1 1, col2 foo
688 NOTICE: col1 2, col2 bar
689 NOTICE: action: return
695 select tcl_spi_exec(false, 'break');
696 NOTICE: col1 1, col2 foo
697 NOTICE: col1 2, col2 bar
698 NOTICE: action: break
699 NOTICE: end of function
705 select tcl_spi_exec(false, 'continue');
706 NOTICE: col1 1, col2 foo
707 NOTICE: col1 2, col2 bar
708 NOTICE: action: continue
709 NOTICE: col1 3, col2 baz
710 NOTICE: end of function
716 select tcl_spi_exec(false, 'error');
717 NOTICE: col1 1, col2 foo
718 NOTICE: col1 2, col2 bar
719 NOTICE: action: error
721 select tcl_spi_exec(false, 'return');
722 NOTICE: col1 1, col2 foo
723 NOTICE: col1 2, col2 bar
724 NOTICE: action: return
730 -- forcibly run the Tcl event loop for awhile, to check that we have not
731 -- messed things up too badly by disabling the Tcl notifier subsystem
733 unset -nocomplain ::tcl_vwait
734 after 100 {set ::tcl_vwait 1}
736 unset -nocomplain ::tcl_vwait$$);
742 -- test transition table visibility
743 create table transition_table_test (id int, name text);
744 insert into transition_table_test values (1, 'a');
745 create function transition_table_test_f() returns trigger language pltcl as
747 spi_exec -array C "SELECT id, name FROM old_table" {
748 elog INFO "old: $C(id) -> $C(name)"
750 spi_exec -array C "SELECT id, name FROM new_table" {
751 elog INFO "new: $C(id) -> $C(name)"
755 CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test
756 REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
757 FOR EACH STATEMENT EXECUTE PROCEDURE transition_table_test_f();
758 update transition_table_test set name = 'b';
761 drop table transition_table_test;
762 drop function transition_table_test_f();