Skip site navigation (1) Skip section navigation (2)

Re: Perl trigger not working

From: Jignesh Shah <jignesh(dot)shah1980(at)gmail(dot)com>
To: Michael Wood <esiotrot(at)gmail(dot)com>
Cc: postgresql novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Perl trigger not working
Date: 2009-08-27 05:18:13
Message-ID: c11950270908262218jfee88c1i79111d625204f786@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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>
>

In response to

Responses

pgsql-novice by date

Next:From: Jignesh ShahDate: 2009-08-27 06:37:25
Subject: Re: Install new perl test function in PostgreSQL
Previous:From: David SaraciniDate: 2009-08-27 02:20:52
Subject: Re: pg_dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group