src/pl/plperl/expected/plperl_trigger.out - master - githubmirror/postgres

src/pl/plperl/expected/plperl_trigger.out
392 строки15 KB
Peter Eisentraut
Virtual generated columns
07 фев 2025, 11:46
07 фев 2025, 11:4683ea6c5
-- 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,
    k int GENERATED ALWAYS AS (i * 3) VIRTUAL
);
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 | k 
---+---+---
(0 rows)