[Cialug] Postgres trigger help.

[Kevin C. Smith]

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