[Cialug] Postgres trigger help.

[Kevin C. Smith]

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.



-- 
Religion is regarded by the common people as true, by the wise as false,
and by the rulers as useful. --- Lucius Annaeus Seneca