1 -- test plperl triggers
2 CREATE TABLE trigger_test (
6 CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger LANGUAGE plperl AS $$
8 # make sure keys are sorted for consistent results - perl no longer
9 # hashes in repeatable fashion across runs
11 foreach my $key (sort keys %$_TD)
14 my $val = $_TD->{$key};
16 # relid is variable, so we can not use it repeatably
17 $val = "bogus:12345" if $key eq 'relid';
21 elog(NOTICE, "\$_TD->\{$key\} = NULL");
25 elog(NOTICE, "\$_TD->\{$key\} = '$val'");
27 elsif (ref $val eq 'HASH')
30 foreach my $rowkey (sort keys %$val)
33 my $rowval = $val->{$rowkey};
34 $str .= "'$rowkey' => '$rowval'";
36 elog(NOTICE, "\$_TD->\{$key\} = \{$str\}");
38 elsif (ref $val eq 'ARRAY')
41 foreach my $argval (@$val)
46 elog(NOTICE, "\$_TD->\{$key\} = \[$str\]");
49 return undef; # allow statement to proceed;
51 CREATE TRIGGER show_trigger_data_trig
52 BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
53 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
54 insert into trigger_test values(1,'insert');
55 NOTICE: $_TD->{argc} = '2'
56 NOTICE: $_TD->{args} = ['23', 'skidoo']
57 NOTICE: $_TD->{event} = 'INSERT'
58 NOTICE: $_TD->{level} = 'ROW'
59 NOTICE: $_TD->{name} = 'show_trigger_data_trig'
60 NOTICE: $_TD->{new} = {'i' => '1', 'v' => 'insert'}
61 NOTICE: $_TD->{relid} = 'bogus:12345'
62 NOTICE: $_TD->{relname} = 'trigger_test'
63 NOTICE: $_TD->{table_name} = 'trigger_test'
64 NOTICE: $_TD->{table_schema} = 'public'
65 NOTICE: $_TD->{when} = 'BEFORE'
66 update trigger_test set v = 'update' where i = 1;
67 NOTICE: $_TD->{argc} = '2'
68 NOTICE: $_TD->{args} = ['23', 'skidoo']
69 NOTICE: $_TD->{event} = 'UPDATE'
70 NOTICE: $_TD->{level} = 'ROW'
71 NOTICE: $_TD->{name} = 'show_trigger_data_trig'
72 NOTICE: $_TD->{new} = {'i' => '1', 'v' => 'update'}
73 NOTICE: $_TD->{old} = {'i' => '1', 'v' => 'insert'}
74 NOTICE: $_TD->{relid} = 'bogus:12345'
75 NOTICE: $_TD->{relname} = 'trigger_test'
76 NOTICE: $_TD->{table_name} = 'trigger_test'
77 NOTICE: $_TD->{table_schema} = 'public'
78 NOTICE: $_TD->{when} = 'BEFORE'
79 delete from trigger_test;
80 NOTICE: $_TD->{argc} = '2'
81 NOTICE: $_TD->{args} = ['23', 'skidoo']
82 NOTICE: $_TD->{event} = 'DELETE'
83 NOTICE: $_TD->{level} = 'ROW'
84 NOTICE: $_TD->{name} = 'show_trigger_data_trig'
85 NOTICE: $_TD->{old} = {'i' => '1', 'v' => 'update'}
86 NOTICE: $_TD->{relid} = 'bogus:12345'
87 NOTICE: $_TD->{relname} = 'trigger_test'
88 NOTICE: $_TD->{table_name} = 'trigger_test'
89 NOTICE: $_TD->{table_schema} = 'public'
90 NOTICE: $_TD->{when} = 'BEFORE'
92 DROP TRIGGER show_trigger_data_trig on trigger_test;
94 DROP FUNCTION trigger_data();
95 CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
97 if (($_TD->{new}{i}>=100) || ($_TD->{new}{i}<=0))
99 return "SKIP"; # Skip INSERT/UPDATE command
101 elsif ($_TD->{new}{v} ne "immortal")
103 $_TD->{new}{v} .= "(modified by trigger)";
104 return "MODIFY"; # Modify tuple and proceed INSERT/UPDATE command
108 return; # Proceed INSERT/UPDATE command
111 CREATE TRIGGER "test_valid_id_trig" BEFORE INSERT OR UPDATE ON trigger_test
112 FOR EACH ROW EXECUTE PROCEDURE "valid_id"();
113 INSERT INTO trigger_test (i, v) VALUES (1,'first line');
114 INSERT INTO trigger_test (i, v) VALUES (2,'second line');
115 INSERT INTO trigger_test (i, v) VALUES (3,'third line');
116 INSERT INTO trigger_test (i, v) VALUES (4,'immortal');
117 INSERT INTO trigger_test (i, v) VALUES (101,'bad id');
118 SELECT * FROM trigger_test;
120 ---+----------------------------------
121 1 | first line(modified by trigger)
122 2 | second line(modified by trigger)
123 3 | third line(modified by trigger)
127 UPDATE trigger_test SET i = 5 where i=3;
128 UPDATE trigger_test SET i = 100 where i=1;
129 SELECT * FROM trigger_test;
131 ---+------------------------------------------------------
132 1 | first line(modified by trigger)
133 2 | second line(modified by trigger)
135 5 | third line(modified by trigger)(modified by trigger)
138 CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$
139 if ($_TD->{old}{v} eq $_TD->{args}[0])
141 return "SKIP"; # Skip DELETE command
145 return; # Proceed DELETE command
148 CREATE TRIGGER "immortal_trig" BEFORE DELETE ON trigger_test
149 FOR EACH ROW EXECUTE PROCEDURE immortal('immortal');
150 DELETE FROM trigger_test;
151 SELECT * FROM trigger_test;