Thread: Perl trigger not working

2009/8/26 Jignesh Shah <jignesh.shah1980@gmail.com>:

> I have taken ditto perl trigger example from PostgreSQL documentation
> Chapter 40
> (http://developer.postgresql.org/pgdocs/postgres/plperl-triggers.html) and
> installed on my database but it not printing anything and also not inserting
> any rows in "test" table. See below. Please help me out with it. What I am
> doing wrong?
>
> mydb=# CREATE TABLE test (
> mydb(# i int,
> mydb(# v varchar
> mydb(# );
> CREATE TABLE
> mydb=# CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
> mydb$# if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) {
> mydb$# return "SKIP"; # skip INSERT/UPDATE command
> mydb$# } elsif ($_TD->{new}{v} ne "immortal") {
> mydb$# $_TD->{new}{v} .= "(modified by trigger)";
> mydb$# return "MODIFY"; # modify row and execute INSERT/UPDATE command
> mydb$# } else {
> mydb$# return; # execute INSERT/UPDATE command
> mydb$# }
> mydb$# $$ LANGUAGE plperl;
> CREATE FUNCTION
> mydb=# CREATE TRIGGER test_valid_id_trig
> mydb-# BEFORE INSERT OR UPDATE ON test
> mydb-# FOR EACH ROW EXECUTE PROCEDURE valid_id();
> CREATE TRIGGER
> mydb=# insert INTO test VALUES(100, 'c');
> INSERT 0 0
> mydb=# insert INTO test VALUES(200, 'c');
> INSERT 0 0
> mydb=# select * from test;

What happens if you:
insert into test values (44, 'c');
insert into test values (55, 'immortal');

--
Michael Wood <esiotrot@gmail.com>

Thanks Michael. It works. You are really helped me a lot. Thanks a ton for that.

 

Could you please tell mw if there is any meaning of returning "MODIFY" or "SKIP" string from function? Who will see and takes care of that return value?

 

mydb=# insert INTO test VALUES(44, 'c');
INSERT 0 1
mydb=# insert INTO test VALUES(55, 'immortal');
INSERT 0 1
mydb=# select * from test;
 44 | c(modified by trigger)
 55 | immortal

mydb=#

Thanks,

Jignesh

 

On Wed, Aug 26, 2009 at 10:21 PM, Michael Wood <esiotrot@gmail.com> wrote:

2009/8/26 Jignesh Shah <jignesh.shah1980@gmail.com>:

> I have taken ditto perl trigger example from PostgreSQL documentation
> Chapter 40
> (http://developer.postgresql.org/pgdocs/postgres/plperl-triggers.html) and
> installed on my database but it not printing anything and also not inserting
> any rows in "test" table. See below. Please help me out with it. What I am
> doing wrong?
>
> mydb=# CREATE TABLE test (
> mydb(# i int,
> mydb(# v varchar
> mydb(# );
> CREATE TABLE
> mydb=# CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
> mydb$# if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) {
> mydb$# return "SKIP"; # skip INSERT/UPDATE command
> mydb$# } elsif ($_TD->{new}{v} ne "immortal") {
> mydb$# $_TD->{new}{v} .= "(modified by trigger)";
> mydb$# return "MODIFY"; # modify row and execute INSERT/UPDATE command
> mydb$# } else {
> mydb$# return; # execute INSERT/UPDATE command
> mydb$# }
> mydb$# $$ LANGUAGE plperl;
> CREATE FUNCTION
> mydb=# CREATE TRIGGER test_valid_id_trig
> mydb-# BEFORE INSERT OR UPDATE ON test
> mydb-# FOR EACH ROW EXECUTE PROCEDURE valid_id();
> CREATE TRIGGER
> mydb=# insert INTO test VALUES(100, 'c');
> INSERT 0 0
> mydb=# insert INTO test VALUES(200, 'c');
> INSERT 0 0
> mydb=# select * from test;

What happens if you:
insert into test values (44, 'c');
insert into test values (55, 'immortal');

--
Michael Wood <esiotrot@gmail.com>