[Cialug] Postgres trigger help.

[Tim Wilson]

That's interesting.  I didn't get into an infinite loop, but the column
wasn't updated either.

After changing it to BEFORE instead of AFTER, it worked, including on
insert.  And I didn't need to change opaque to trigger.  Thanks for
everyone's help.

On 8/14/07, Kevin C. Smith <kevin at linuxsmith.com> wrote:
>
> On Tue, 2007-08-14 at 19:51 -0500, Kevin C. Smith wrote:
> > On Tue, 2007-08-14 at 18:22 -0500, Tim Wilson wrote:
> > >
> > >
> > > On 8/14/07, Kevin C. Smith <kevin at linuxsmith.com> wrote:
> > >         >
> > >         > CREATE FUNCTION set_last_update() RETURNS opaque AS '
> > >         > BEGIN
> > >         > NEW.last_updated:=now();
> > >         > RETURN NEW;
> > >         > END;
> > >         > ' LANGUAGE 'plpgsql';
> > >         >
> > >         > CREATE TRIGGER set_last_update AFTER INSERT OR UPDATE ON
> > >         people FOR EACH
> > >         > ROW
> > >         > EXECUTE PROCEDURE set_last_update();
> > >         >
> > >
> > >         Okay I see maybe two issues (disclaimer I don't know what the
> > >         hell I'm
> > >         talking
> > >         about).
> > >
> > >         Wouldn't a trigger AFTER insert or update become in infinite
> > >         loop, and maybe
> > >         postgresql therefore ignores it? Just a guess.
> > >
> > > I thought trigger updates were different, but I  guess it's possible.
> > >
> > >
> > >         I also think that were you have 'opaque' you need 'trigger'.
> > >
> > >
> > > The 'trigger' type caused the parser to complain.  However, I'll try
> > > it again when I can.  It's possible the other things I did caused
> > > 'trigger' to be valid.  But my guess is 'opaque' works in 7.1, but
> > > 'trigger' was added later.
> > >
> > >
> > >         I tested this and it worked:
> > >
> > >
> > >         CREATE OR REPLACE FUNCTION set_last_update()
> > >           RETURNS "trigger" AS
> > >         BEGIN
> > >           NEW.last_updated = now();
> > >           RETURN NEW;
> > >         END;
> > >           LANGUAGE 'plpgsql' VOLATILE;
> > >
> > >
> > >         CREATE TRIGGER set_last_update
> > >           BEFORE INSERT OR UPDATE
> > >           ON test_table
> > >           FOR EACH ROW
> > >           EXECUTE PROCEDURE set_last_update();
> > >
> > >
> > > I chose AFTER because I didn't want the timestamp to be affected if
> > > for some reason the update failed.  I also thought if it happened
> > > before the insert, what would it be updating, since the record didn't
> > > exist yet.
> >
> > I wondered about INSERT also, but seems to work fine. Give it a try.
> > Not sure how it's actually being handled, but it works.
> >
> > AFTER update looks like a loop to me, since the trigger fires
> > on insert or update of a row and itself updates a row in the same
> > table it watches for updates.
> >
> > Yeah, I forgot to mention that I'm using version 8.2.4, so it may
> > be slightly different for your situation.
> >
> > Thanks for the exercise!
> > I'd been meaning to try out triggers in Postgresql,
> > after my trying some in MySQL.
>
> P.S.
> Searched for "postgresql after update trigger loop" on google and found
> this:
>
> http://blog.revsys.com/2006/08/automatically_u.html
>
> Wish he would have reference something authoritative on that the
> loop question.
>
>
>
> --
> Religion is regarded by the common people as true, by the wise as false,
> and by the rulers as useful. --- Lucius Annaeus Seneca
>
> _______________________________________________
> Cialug mailing list
> Cialug at cialug.org
> http://cialug.org/mailman/listinfo/cialug
>



-- 
Tim
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://cialug.org/pipermail/cialug/attachments/20070814/860c2abf/attachment.html