summaryrefslogtreecommitdiffstats
path: root/src/pl/plperl/expected/plperl_trigger.out
diff options
context:
space:
mode:
Diffstat (limited to 'src/pl/plperl/expected/plperl_trigger.out')
-rw-r--r--src/pl/plperl/expected/plperl_trigger.out392
1 files changed, 392 insertions, 0 deletions
diff --git a/src/pl/plperl/expected/plperl_trigger.out b/src/pl/plperl/expected/plperl_trigger.out
new file mode 100644
index 0000000..d4879e2
--- /dev/null
+++ b/src/pl/plperl/expected/plperl_trigger.out
@@ -0,0 +1,392 @@
+-- test plperl triggers
+CREATE TYPE rowcomp as (i int);
+CREATE TYPE rowcompnest as (rfoo rowcomp);
+CREATE TABLE trigger_test (
+ i int,
+ v varchar,
+ foo rowcompnest
+);
+CREATE TABLE trigger_test_generated (
+ i int,
+ j int GENERATED ALWAYS AS (i * 2) STORED
+);
+CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger LANGUAGE plperl AS $$
+
+ # make sure keys are sorted for consistent results - perl no longer
+ # hashes in repeatable fashion across runs
+
+ sub str {
+ my $val = shift;
+
+ if (!defined $val)
+ {
+ return 'NULL';
+ }
+ elsif (ref $val eq 'HASH')
+ {
+ my $str = '';
+ foreach my $rowkey (sort keys %$val)
+ {
+ $str .= ", " if $str;
+ my $rowval = str($val->{$rowkey});
+ $str .= "'$rowkey' => $rowval";
+ }
+ return '{'. $str .'}';
+ }
+ elsif (ref $val eq 'ARRAY')
+ {
+ my $str = '';
+ for my $argval (@$val)
+ {
+ $str .= ", " if $str;
+ $str .= str($argval);
+ }
+ return '['. $str .']';
+ }
+ else
+ {
+ return "'$val'";
+ }
+ }
+
+ foreach my $key (sort keys %$_TD)
+ {
+
+ my $val = $_TD->{$key};
+
+ # relid is variable, so we can not use it repeatably
+ $val = "bogus:12345" if $key eq 'relid';
+
+ elog(NOTICE, "\$_TD->\{$key\} = ". str($val));
+ }
+ return undef; # allow statement to proceed;
+$$;
+CREATE TRIGGER show_trigger_data_trig
+BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+insert into trigger_test values(1,'insert', '("(1)")');
+NOTICE: $_TD->{argc} = '2'
+NOTICE: $_TD->{args} = ['23', 'skidoo']
+NOTICE: $_TD->{event} = 'INSERT'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig'
+NOTICE: $_TD->{new} = {'foo' => {'rfoo' => {'i' => '1'}}, 'i' => '1', 'v' => 'insert'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test'
+NOTICE: $_TD->{table_name} = 'trigger_test'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'BEFORE'
+update trigger_test set v = 'update' where i = 1;
+NOTICE: $_TD->{argc} = '2'
+NOTICE: $_TD->{args} = ['23', 'skidoo']
+NOTICE: $_TD->{event} = 'UPDATE'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig'
+NOTICE: $_TD->{new} = {'foo' => {'rfoo' => {'i' => '1'}}, 'i' => '1', 'v' => 'update'}
+NOTICE: $_TD->{old} = {'foo' => {'rfoo' => {'i' => '1'}}, 'i' => '1', 'v' => 'insert'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test'
+NOTICE: $_TD->{table_name} = 'trigger_test'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'BEFORE'
+delete from trigger_test;
+NOTICE: $_TD->{argc} = '2'
+NOTICE: $_TD->{args} = ['23', 'skidoo']
+NOTICE: $_TD->{event} = 'DELETE'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig'
+NOTICE: $_TD->{old} = {'foo' => {'rfoo' => {'i' => '1'}}, 'i' => '1', 'v' => 'update'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test'
+NOTICE: $_TD->{table_name} = 'trigger_test'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'BEFORE'
+DROP TRIGGER show_trigger_data_trig on trigger_test;
+CREATE TRIGGER show_trigger_data_trig_before
+BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated
+FOR EACH ROW EXECUTE PROCEDURE trigger_data();
+CREATE TRIGGER show_trigger_data_trig_after
+AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated
+FOR EACH ROW EXECUTE PROCEDURE trigger_data();
+insert into trigger_test_generated (i) values (1);
+NOTICE: $_TD->{argc} = '0'
+NOTICE: $_TD->{event} = 'INSERT'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig_before'
+NOTICE: $_TD->{new} = {'i' => '1'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_generated'
+NOTICE: $_TD->{table_name} = 'trigger_test_generated'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'BEFORE'
+NOTICE: $_TD->{argc} = '0'
+NOTICE: $_TD->{event} = 'INSERT'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig_after'
+NOTICE: $_TD->{new} = {'i' => '1', 'j' => '2'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_generated'
+NOTICE: $_TD->{table_name} = 'trigger_test_generated'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'AFTER'
+update trigger_test_generated set i = 11 where i = 1;
+NOTICE: $_TD->{argc} = '0'
+NOTICE: $_TD->{event} = 'UPDATE'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig_before'
+NOTICE: $_TD->{new} = {'i' => '11'}
+NOTICE: $_TD->{old} = {'i' => '1', 'j' => '2'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_generated'
+NOTICE: $_TD->{table_name} = 'trigger_test_generated'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'BEFORE'
+NOTICE: $_TD->{argc} = '0'
+NOTICE: $_TD->{event} = 'UPDATE'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig_after'
+NOTICE: $_TD->{new} = {'i' => '11', 'j' => '22'}
+NOTICE: $_TD->{old} = {'i' => '1', 'j' => '2'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_generated'
+NOTICE: $_TD->{table_name} = 'trigger_test_generated'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'AFTER'
+delete from trigger_test_generated;
+NOTICE: $_TD->{argc} = '0'
+NOTICE: $_TD->{event} = 'DELETE'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig_before'
+NOTICE: $_TD->{old} = {'i' => '11', 'j' => '22'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_generated'
+NOTICE: $_TD->{table_name} = 'trigger_test_generated'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'BEFORE'
+NOTICE: $_TD->{argc} = '0'
+NOTICE: $_TD->{event} = 'DELETE'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig_after'
+NOTICE: $_TD->{old} = {'i' => '11', 'j' => '22'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_generated'
+NOTICE: $_TD->{table_name} = 'trigger_test_generated'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'AFTER'
+DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated;
+DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated;
+insert into trigger_test values(1,'insert', '("(1)")');
+CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test;
+CREATE TRIGGER show_trigger_data_trig
+INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view');
+insert into trigger_test_view values(2,'insert', '("(2)")');
+NOTICE: $_TD->{argc} = '2'
+NOTICE: $_TD->{args} = ['24', 'skidoo view']
+NOTICE: $_TD->{event} = 'INSERT'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig'
+NOTICE: $_TD->{new} = {'foo' => {'rfoo' => {'i' => '2'}}, 'i' => '2', 'v' => 'insert'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_view'
+NOTICE: $_TD->{table_name} = 'trigger_test_view'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'INSTEAD OF'
+update trigger_test_view set v = 'update', foo = '("(3)")' where i = 1;
+NOTICE: $_TD->{argc} = '2'
+NOTICE: $_TD->{args} = ['24', 'skidoo view']
+NOTICE: $_TD->{event} = 'UPDATE'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig'
+NOTICE: $_TD->{new} = {'foo' => {'rfoo' => {'i' => '3'}}, 'i' => '1', 'v' => 'update'}
+NOTICE: $_TD->{old} = {'foo' => {'rfoo' => {'i' => '1'}}, 'i' => '1', 'v' => 'insert'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_view'
+NOTICE: $_TD->{table_name} = 'trigger_test_view'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'INSTEAD OF'
+delete from trigger_test_view;
+NOTICE: $_TD->{argc} = '2'
+NOTICE: $_TD->{args} = ['24', 'skidoo view']
+NOTICE: $_TD->{event} = 'DELETE'
+NOTICE: $_TD->{level} = 'ROW'
+NOTICE: $_TD->{name} = 'show_trigger_data_trig'
+NOTICE: $_TD->{old} = {'foo' => {'rfoo' => {'i' => '1'}}, 'i' => '1', 'v' => 'insert'}
+NOTICE: $_TD->{relid} = 'bogus:12345'
+NOTICE: $_TD->{relname} = 'trigger_test_view'
+NOTICE: $_TD->{table_name} = 'trigger_test_view'
+NOTICE: $_TD->{table_schema} = 'public'
+NOTICE: $_TD->{when} = 'INSTEAD OF'
+DROP VIEW trigger_test_view;
+delete from trigger_test;
+DROP FUNCTION trigger_data();
+CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
+
+ if (($_TD->{new}{i}>=100) || ($_TD->{new}{i}<=0))
+ {
+ return "SKIP"; # Skip INSERT/UPDATE command
+ }
+ elsif ($_TD->{new}{v} ne "immortal")
+ {
+ $_TD->{new}{v} .= "(modified by trigger)";
+ $_TD->{new}{foo}{rfoo}{i}++;
+ return "MODIFY"; # Modify tuple and proceed INSERT/UPDATE command
+ }
+ else
+ {
+ return; # Proceed INSERT/UPDATE command
+ }
+$$ LANGUAGE plperl;
+CREATE TRIGGER "test_valid_id_trig" BEFORE INSERT OR UPDATE ON trigger_test
+FOR EACH ROW EXECUTE PROCEDURE "valid_id"();
+INSERT INTO trigger_test (i, v, foo) VALUES (1,'first line', '("(1)")');
+INSERT INTO trigger_test (i, v, foo) VALUES (2,'second line', '("(2)")');
+INSERT INTO trigger_test (i, v, foo) VALUES (3,'third line', '("(3)")');
+INSERT INTO trigger_test (i, v, foo) VALUES (4,'immortal', '("(4)")');
+INSERT INTO trigger_test (i, v) VALUES (101,'bad id');
+SELECT * FROM trigger_test;
+ i | v | foo
+---+----------------------------------+---------
+ 1 | first line(modified by trigger) | ("(2)")
+ 2 | second line(modified by trigger) | ("(3)")
+ 3 | third line(modified by trigger) | ("(4)")
+ 4 | immortal | ("(4)")
+(4 rows)
+
+UPDATE trigger_test SET i = 5 where i=3;
+UPDATE trigger_test SET i = 100 where i=1;
+SELECT * FROM trigger_test;
+ i | v | foo
+---+------------------------------------------------------+---------
+ 1 | first line(modified by trigger) | ("(2)")
+ 2 | second line(modified by trigger) | ("(3)")
+ 4 | immortal | ("(4)")
+ 5 | third line(modified by trigger)(modified by trigger) | ("(5)")
+(4 rows)
+
+DROP TRIGGER "test_valid_id_trig" ON trigger_test;
+CREATE OR REPLACE FUNCTION trigger_recurse() RETURNS trigger AS $$
+ use strict;
+
+ if ($_TD->{new}{i} == 10000)
+ {
+ spi_exec_query("insert into trigger_test (i, v) values (20000, 'child');");
+
+ if ($_TD->{new}{i} != 10000)
+ {
+ die "recursive trigger modified: ". $_TD->{new}{i};
+ }
+ }
+ return;
+$$ LANGUAGE plperl;
+CREATE TRIGGER "test_trigger_recurse" BEFORE INSERT ON trigger_test
+FOR EACH ROW EXECUTE PROCEDURE "trigger_recurse"();
+INSERT INTO trigger_test (i, v) values (10000, 'top');
+SELECT * FROM trigger_test;
+ i | v | foo
+-------+------------------------------------------------------+---------
+ 1 | first line(modified by trigger) | ("(2)")
+ 2 | second line(modified by trigger) | ("(3)")
+ 4 | immortal | ("(4)")
+ 5 | third line(modified by trigger)(modified by trigger) | ("(5)")
+ 20000 | child |
+ 10000 | top |
+(6 rows)
+
+CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$
+ if ($_TD->{old}{v} eq $_TD->{args}[0])
+ {
+ return "SKIP"; # Skip DELETE command
+ }
+ else
+ {
+ return; # Proceed DELETE command
+ };
+$$ LANGUAGE plperl;
+CREATE TRIGGER "immortal_trig" BEFORE DELETE ON trigger_test
+FOR EACH ROW EXECUTE PROCEDURE immortal('immortal');
+DELETE FROM trigger_test;
+SELECT * FROM trigger_test;
+ i | v | foo
+---+----------+---------
+ 4 | immortal | ("(4)")
+(1 row)
+
+CREATE FUNCTION direct_trigger() RETURNS trigger AS $$
+ return;
+$$ LANGUAGE plperl;
+SELECT direct_trigger();
+ERROR: trigger functions can only be called as triggers
+CONTEXT: compilation of PL/Perl function "direct_trigger"
+-- check that SQL run in trigger code can see transition tables
+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 plperl AS
+$$
+ my $cursor = spi_query("SELECT * FROM old_table");
+ my $row = spi_fetchrow($cursor);
+ defined($row) || die "expected a row";
+ elog(INFO, "old: " . $row->{id} . " -> " . $row->{name});
+ my $row = spi_fetchrow($cursor);
+ !defined($row) || die "expected no more rows";
+
+ my $cursor = spi_query("SELECT * FROM new_table");
+ my $row = spi_fetchrow($cursor);
+ defined($row) || die "expected a row";
+ elog(INFO, "new: " . $row->{id} . " -> " . $row->{name});
+ my $row = spi_fetchrow($cursor);
+ !defined($row) || die "expected no more rows";
+
+ return undef;
+$$;
+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();
+-- test plperl command triggers
+create or replace function perlsnitch() returns event_trigger language plperl as $$
+ elog(NOTICE, "perlsnitch: " . $_TD->{event} . " " . $_TD->{tag} . " ");
+$$;
+create event trigger perl_a_snitch on ddl_command_start
+ execute procedure perlsnitch();
+create event trigger perl_b_snitch on ddl_command_end
+ execute procedure perlsnitch();
+create or replace function foobar() returns int language sql as $$select 1;$$;
+NOTICE: perlsnitch: ddl_command_start CREATE FUNCTION
+NOTICE: perlsnitch: ddl_command_end CREATE FUNCTION
+alter function foobar() cost 77;
+NOTICE: perlsnitch: ddl_command_start ALTER FUNCTION
+NOTICE: perlsnitch: ddl_command_end ALTER FUNCTION
+drop function foobar();
+NOTICE: perlsnitch: ddl_command_start DROP FUNCTION
+NOTICE: perlsnitch: ddl_command_end DROP FUNCTION
+create table foo();
+NOTICE: perlsnitch: ddl_command_start CREATE TABLE
+NOTICE: perlsnitch: ddl_command_end CREATE TABLE
+drop table foo;
+NOTICE: perlsnitch: ddl_command_start DROP TABLE
+NOTICE: perlsnitch: ddl_command_end DROP TABLE
+drop event trigger perl_a_snitch;
+drop event trigger perl_b_snitch;
+-- dealing with generated columns
+CREATE FUNCTION generated_test_func1() RETURNS trigger
+LANGUAGE plperl
+AS $$
+$_TD->{new}{j} = 5; # not allowed
+return 'MODIFY';
+$$;
+CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated
+FOR EACH ROW EXECUTE PROCEDURE generated_test_func1();
+TRUNCATE trigger_test_generated;
+INSERT INTO trigger_test_generated (i) VALUES (1);
+ERROR: cannot set generated column "j"
+CONTEXT: PL/Perl function "generated_test_func1"
+SELECT * FROM trigger_test_generated;
+ i | j
+---+---
+(0 rows)
+
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-04 06:33:38 +0000