Re: Perl trigger not working

I got the meaning of each strings from Chapter 40.6. Thanks.

Row-level triggers can return one of the following:
return;
Execute the operation

"SKIP"
Don’t execute the operation

"MODIFY"
Indicates that the NEW row was modified by the trigger function
Thanks,
Jignesh
On Thu, Aug 27, 2009 at 10:48 AM, Jignesh Shah
<jignesh(dot)shah1980(at)gmail(dot)com>wrote:

> 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(at)gmail(dot)com> wrote:
>
>> 2009/8/26 Jignesh Shah <jignesh(dot)shah1980(at)gmail(dot)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(at)gmail(dot)com>
>>
>
>