[Cialug] Postgres trigger help.

[Tim Wilson]

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.


Good luck
>
> --
> Kevin C. Smith
>
> _______________________________________________
> 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/6708970d/attachment.htm