diff options
Diffstat (limited to 'src/pl/tcl/expected/pltcl_queries.out')
| -rw-r--r-- | src/pl/tcl/expected/pltcl_queries.out | 383 |
1 files changed, 9 insertions, 374 deletions
diff --git a/src/pl/tcl/expected/pltcl_queries.out b/src/pl/tcl/expected/pltcl_queries.out index 17e821bb4cf..2d922c2333e 100644 --- a/src/pl/tcl/expected/pltcl_queries.out +++ b/src/pl/tcl/expected/pltcl_queries.out @@ -1,319 +1,5 @@ -- suppress CONTEXT so that function OIDs aren't in output \set VERBOSITY terse -insert into T_pkey1 values (1, 'key1-1', 'test key'); -insert into T_pkey1 values (1, 'key1-2', 'test key'); -insert into T_pkey1 values (1, 'key1-3', 'test key'); -insert into T_pkey1 values (2, 'key2-1', 'test key'); -insert into T_pkey1 values (2, 'key2-2', 'test key'); -insert into T_pkey1 values (2, 'key2-3', 'test key'); -insert into T_pkey2 values (1, 'key1-1', 'test key'); -insert into T_pkey2 values (1, 'key1-2', 'test key'); -insert into T_pkey2 values (1, 'key1-3', 'test key'); -insert into T_pkey2 values (2, 'key2-1', 'test key'); -insert into T_pkey2 values (2, 'key2-2', 'test key'); -insert into T_pkey2 values (2, 'key2-3', 'test key'); -select * from T_pkey1; - key1 | key2 | txt -------+----------------------+------------------------------------------ - 1 | key1-1 | test key - 1 | key1-2 | test key - 1 | key1-3 | test key - 2 | key2-1 | test key - 2 | key2-2 | test key - 2 | key2-3 | test key -(6 rows) - --- key2 in T_pkey2 should have upper case only -select * from T_pkey2; - key1 | key2 | txt -------+----------------------+------------------------------------------ - 1 | KEY1-1 | test key - 1 | KEY1-2 | test key - 1 | KEY1-3 | test key - 2 | KEY2-1 | test key - 2 | KEY2-2 | test key - 2 | KEY2-3 | test key -(6 rows) - -insert into T_pkey1 values (1, 'KEY1-3', 'should work'); --- Due to the upper case translation in trigger this must fail -insert into T_pkey2 values (1, 'KEY1-3', 'should fail'); -ERROR: duplicate key '1', 'KEY1-3' for T_pkey2 -insert into T_dta1 values ('trec 1', 1, 'key1-1'); -insert into T_dta1 values ('trec 2', 1, 'key1-2'); -insert into T_dta1 values ('trec 3', 1, 'key1-3'); --- Must fail due to unknown key in T_pkey1 -insert into T_dta1 values ('trec 4', 1, 'key1-4'); -ERROR: key for t_dta1 not in t_pkey1 -insert into T_dta2 values ('trec 1', 1, 'KEY1-1'); -insert into T_dta2 values ('trec 2', 1, 'KEY1-2'); -insert into T_dta2 values ('trec 3', 1, 'KEY1-3'); --- Must fail due to unknown key in T_pkey2 -insert into T_dta2 values ('trec 4', 1, 'KEY1-4'); -ERROR: key for t_dta2 not in t_pkey2 -select * from T_dta1; - tkey | ref1 | ref2 -------------+------+---------------------- - trec 1 | 1 | key1-1 - trec 2 | 1 | key1-2 - trec 3 | 1 | key1-3 -(3 rows) - -select * from T_dta2; - tkey | ref1 | ref2 -------------+------+---------------------- - trec 1 | 1 | KEY1-1 - trec 2 | 1 | KEY1-2 - trec 3 | 1 | KEY1-3 -(3 rows) - -update T_pkey1 set key2 = 'key2-9' where key1 = 2 and key2 = 'key2-1'; -update T_pkey1 set key2 = 'key1-9' where key1 = 1 and key2 = 'key1-1'; -ERROR: key '1', 'key1-1 ' referenced by T_dta1 -delete from T_pkey1 where key1 = 2 and key2 = 'key2-2'; -delete from T_pkey1 where key1 = 1 and key2 = 'key1-2'; -ERROR: key '1', 'key1-2 ' referenced by T_dta1 -update T_pkey2 set key2 = 'KEY2-9' where key1 = 2 and key2 = 'KEY2-1'; -update T_pkey2 set key2 = 'KEY1-9' where key1 = 1 and key2 = 'KEY1-1'; -NOTICE: updated 1 entries in T_dta2 for new key in T_pkey2 -delete from T_pkey2 where key1 = 2 and key2 = 'KEY2-2'; -delete from T_pkey2 where key1 = 1 and key2 = 'KEY1-2'; -NOTICE: deleted 1 entries from T_dta2 -select * from T_pkey1; - key1 | key2 | txt -------+----------------------+------------------------------------------ - 1 | key1-1 | test key - 1 | key1-2 | test key - 1 | key1-3 | test key - 2 | key2-3 | test key - 1 | KEY1-3 | should work - 2 | key2-9 | test key -(6 rows) - -select * from T_pkey2; - key1 | key2 | txt -------+----------------------+------------------------------------------ - 1 | KEY1-3 | test key - 2 | KEY2-3 | test key - 2 | KEY2-9 | test key - 1 | KEY1-9 | test key -(4 rows) - -select * from T_dta1; - tkey | ref1 | ref2 -------------+------+---------------------- - trec 1 | 1 | key1-1 - trec 2 | 1 | key1-2 - trec 3 | 1 | key1-3 -(3 rows) - -select * from T_dta2; - tkey | ref1 | ref2 -------------+------+---------------------- - trec 3 | 1 | KEY1-3 - trec 1 | 1 | KEY1-9 -(2 rows) - -select tcl_avg(key1) from T_pkey1; - tcl_avg ---------- - 1 -(1 row) - -select tcl_sum(key1) from T_pkey1; - tcl_sum ---------- - 8 -(1 row) - -select tcl_avg(key1) from T_pkey2; - tcl_avg ---------- - 1 -(1 row) - -select tcl_sum(key1) from T_pkey2; - tcl_sum ---------- - 6 -(1 row) - --- The following should return NULL instead of 0 -select tcl_avg(key1) from T_pkey1 where key1 = 99; - tcl_avg ---------- - -(1 row) - -select tcl_sum(key1) from T_pkey1 where key1 = 99; - tcl_sum ---------- - 0 -(1 row) - -select 1 @< 2; - ?column? ----------- - t -(1 row) - -select 100 @< 4; - ?column? ----------- - f -(1 row) - -select * from T_pkey1 order by key1 using @<, key2 collate "C"; - key1 | key2 | txt -------+----------------------+------------------------------------------ - 1 | KEY1-3 | should work - 1 | key1-1 | test key - 1 | key1-2 | test key - 1 | key1-3 | test key - 2 | key2-3 | test key - 2 | key2-9 | test key -(6 rows) - -select * from T_pkey2 order by key1 using @<, key2 collate "C"; - key1 | key2 | txt -------+----------------------+------------------------------------------ - 1 | KEY1-3 | test key - 1 | KEY1-9 | test key - 2 | KEY2-3 | test key - 2 | KEY2-9 | test key -(4 rows) - --- show dump of trigger data -insert into trigger_test values(1,'insert'); -NOTICE: NEW: {} -NOTICE: OLD: {} -NOTICE: TG_level: STATEMENT -NOTICE: TG_name: statement_trigger -NOTICE: TG_op: INSERT -NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test -NOTICE: TG_table_schema: public -NOTICE: TG_when: BEFORE -NOTICE: args: {42 {statement trigger}} -NOTICE: NEW: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: insert} -NOTICE: OLD: {} -NOTICE: TG_level: ROW -NOTICE: TG_name: show_trigger_data_trig -NOTICE: TG_op: INSERT -NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test -NOTICE: TG_table_schema: public -NOTICE: TG_when: BEFORE -NOTICE: args: {23 skidoo} -insert into trigger_test_view values(2,'insert'); -NOTICE: NEW: {i: 2, v: insert} -NOTICE: OLD: {} -NOTICE: TG_level: ROW -NOTICE: TG_name: show_trigger_data_view_trig -NOTICE: TG_op: INSERT -NOTICE: TG_relatts: {{} i v} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test_view -NOTICE: TG_table_schema: public -NOTICE: TG_when: {INSTEAD OF} -NOTICE: args: {24 {skidoo view}} -update trigger_test_view set v = 'update' where i=1; -NOTICE: NEW: {i: 1, v: update} -NOTICE: OLD: {i: 1, v: insert} -NOTICE: TG_level: ROW -NOTICE: TG_name: show_trigger_data_view_trig -NOTICE: TG_op: UPDATE -NOTICE: TG_relatts: {{} i v} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test_view -NOTICE: TG_table_schema: public -NOTICE: TG_when: {INSTEAD OF} -NOTICE: args: {24 {skidoo view}} -delete from trigger_test_view; -NOTICE: NEW: {} -NOTICE: OLD: {i: 1, v: insert} -NOTICE: TG_level: ROW -NOTICE: TG_name: show_trigger_data_view_trig -NOTICE: TG_op: DELETE -NOTICE: TG_relatts: {{} i v} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test_view -NOTICE: TG_table_schema: public -NOTICE: TG_when: {INSTEAD OF} -NOTICE: args: {24 {skidoo view}} -update trigger_test set v = 'update', test_skip=true where i = 1; -NOTICE: NEW: {} -NOTICE: OLD: {} -NOTICE: TG_level: STATEMENT -NOTICE: TG_name: statement_trigger -NOTICE: TG_op: UPDATE -NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test -NOTICE: TG_table_schema: public -NOTICE: TG_when: BEFORE -NOTICE: args: {42 {statement trigger}} -NOTICE: SKIPPING OPERATION UPDATE -update trigger_test set v = 'update' where i = 1; -NOTICE: NEW: {} -NOTICE: OLD: {} -NOTICE: TG_level: STATEMENT -NOTICE: TG_name: statement_trigger -NOTICE: TG_op: UPDATE -NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test -NOTICE: TG_table_schema: public -NOTICE: TG_when: BEFORE -NOTICE: args: {42 {statement trigger}} -NOTICE: NEW: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: update} -NOTICE: OLD: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: insert} -NOTICE: TG_level: ROW -NOTICE: TG_name: show_trigger_data_trig -NOTICE: TG_op: UPDATE -NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test -NOTICE: TG_table_schema: public -NOTICE: TG_when: BEFORE -NOTICE: args: {23 skidoo} -delete from trigger_test; -NOTICE: NEW: {} -NOTICE: OLD: {} -NOTICE: TG_level: STATEMENT -NOTICE: TG_name: statement_trigger -NOTICE: TG_op: DELETE -NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test -NOTICE: TG_table_schema: public -NOTICE: TG_when: BEFORE -NOTICE: args: {42 {statement trigger}} -NOTICE: NEW: {} -NOTICE: OLD: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: update} -NOTICE: TG_level: ROW -NOTICE: TG_name: show_trigger_data_trig -NOTICE: TG_op: DELETE -NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test -NOTICE: TG_table_schema: public -NOTICE: TG_when: BEFORE -NOTICE: args: {23 skidoo} -truncate trigger_test; -NOTICE: NEW: {} -NOTICE: OLD: {} -NOTICE: TG_level: STATEMENT -NOTICE: TG_name: statement_trigger -NOTICE: TG_op: TRUNCATE -NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test -NOTICE: TG_table_schema: public -NOTICE: TG_when: BEFORE -NOTICE: args: {42 {statement trigger}} -- Test composite-type arguments select tcl_composite_arg_ref1(row('tkey', 42, 'ref2')); tcl_composite_arg_ref1 @@ -328,17 +14,17 @@ select tcl_composite_arg_ref2(row('tkey', 42, 'ref2')); (1 row) -- More tests for composite argument/result types -create domain d_dta1 as T_dta1 check ((value).ref1 > 0); +create domain d_comp1 as T_comp1 check ((value).ref1 > 0); create function tcl_record_arg(record, fldname text) returns int as ' return $1($2) ' language pltcl; -select tcl_record_arg(row('tkey', 42, 'ref2')::T_dta1, 'ref1'); +select tcl_record_arg(row('tkey', 42, 'ref2')::T_comp1, 'ref1'); tcl_record_arg ---------------- 42 (1 row) -select tcl_record_arg(row('tkey', 42, 'ref2')::d_dta1, 'ref1'); +select tcl_record_arg(row('tkey', 42, 'ref2')::d_comp1, 'ref1'); tcl_record_arg ---------------- 42 @@ -350,7 +36,7 @@ select tcl_record_arg(row(2,4), 'f2'); 4 (1 row) -create function tcl_cdomain_arg(d_dta1) returns int as ' +create function tcl_cdomain_arg(d_comp1) returns int as ' return $1(ref1) ' language pltcl; select tcl_cdomain_arg(row('tkey', 42, 'ref2')); @@ -359,14 +45,14 @@ select tcl_cdomain_arg(row('tkey', 42, 'ref2')); 42 (1 row) -select tcl_cdomain_arg(row('tkey', 42, 'ref2')::T_dta1); +select tcl_cdomain_arg(row('tkey', 42, 'ref2')::T_comp1); tcl_cdomain_arg ----------------- 42 (1 row) select tcl_cdomain_arg(row('tkey', -1, 'ref2')); -- fail -ERROR: value for domain d_dta1 violates check constraint "d_dta1_check" +ERROR: value for domain d_comp1 violates check constraint "d_comp1_check" -- Test argisnull primitive select tcl_argisnull('foo'); tcl_argisnull @@ -386,22 +72,6 @@ select tcl_argisnull(null); t (1 row) --- should error -insert into trigger_test(test_argisnull) values(true); -NOTICE: NEW: {} -NOTICE: OLD: {} -NOTICE: TG_level: STATEMENT -NOTICE: TG_name: statement_trigger -NOTICE: TG_op: INSERT -NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test -NOTICE: TG_table_schema: public -NOTICE: TG_when: BEFORE -NOTICE: args: {42 {statement trigger}} -ERROR: argisnull cannot be used in triggers -select trigger_data(); -ERROR: trigger functions can only be called as triggers -- test some error cases create function tcl_error(out a int, out b int) as $$return {$$ language pltcl; select tcl_error(); @@ -464,7 +134,7 @@ $$ language pltcl; select bad_field_srf(); ERROR: column name/value list contains nonexistent column name "cow" -- test composite and domain-over-composite results -create function tcl_composite_result(int) returns T_dta1 as $$ +create function tcl_composite_result(int) returns T_comp1 as $$ return [list tkey tkey1 ref1 $1 ref2 ref22] $$ language pltcl; select tcl_composite_result(1001); @@ -479,7 +149,7 @@ select * from tcl_composite_result(1002); tkey1 | 1002 | ref22 (1 row) -create function tcl_dcomposite_result(int) returns d_dta1 as $$ +create function tcl_dcomposite_result(int) returns d_comp1 as $$ return [list tkey tkey2 ref1 $1 ref2 ref42] $$ language pltcl; select tcl_dcomposite_result(1001); @@ -495,7 +165,7 @@ select * from tcl_dcomposite_result(1002); (1 row) select * from tcl_dcomposite_result(-1); -- fail -ERROR: value for domain d_dta1 violates check constraint "d_dta1_check" +ERROR: value for domain d_comp1 violates check constraint "d_comp1_check" create function tcl_record_result(int) returns record as $$ return [list q1 sometext q2 $1 q3 moretext] $$ language pltcl; @@ -542,20 +212,6 @@ ERROR: expected integer but got "abc" -- Test return_null select tcl_eval('return_null 14'); ERROR: wrong # args: should be "return_null " --- should error -insert into trigger_test(test_return_null) values(true); -NOTICE: NEW: {} -NOTICE: OLD: {} -NOTICE: TG_level: STATEMENT -NOTICE: TG_name: statement_trigger -NOTICE: TG_op: INSERT -NOTICE: TG_relatts: {{} i v {} test_skip test_return_null test_argisnull} -NOTICE: TG_relid: bogus:12345 -NOTICE: TG_table_name: trigger_test -NOTICE: TG_table_schema: public -NOTICE: TG_when: BEFORE -NOTICE: args: {42 {statement trigger}} -ERROR: return_null cannot be used in triggers -- Test spi_exec select tcl_eval('spi_exec'); ERROR: wrong # args: should be "spi_exec ?-count n? ?-array name? query ?loop body?" @@ -739,24 +395,3 @@ select tcl_eval($$ (1 row) --- test transition table visibility -create table transition_table_test (id int, name text); -insert into transition_table_test values (1, 'a'); -create function transition_table_test_f() returns trigger language pltcl as -$$ - spi_exec -array C "SELECT id, name FROM old_table" { - elog INFO "old: $C(id) -> $C(name)" - } - spi_exec -array C "SELECT id, name FROM new_table" { - elog INFO "new: $C(id) -> $C(name)" - } - return OK -$$; -CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test - REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table - FOR EACH STATEMENT EXECUTE PROCEDURE transition_table_test_f(); -update transition_table_test set name = 'b'; -INFO: old: 1 -> a -INFO: new: 1 -> b -drop table transition_table_test; -drop function transition_table_test_f(); |