Re: 7.2.1 PL/pgSQL issue

On Fri, 12 Apr 2002, Ed Loehr wrote:

> This looks like a bug.  Only question is whose.  The triggered update of a
> timestamp field is not working.  I can see the expected outputs in the log,
> but the timestamp update is not sticking.  Can someone identify my error or
> test this on 7.2.1 and tell me if the bug is mine (most likely) or a bug in
> PL/pgSQL?
>
> -- Simple function to update the 'updated' column to the current time
> CREATE FUNCTION set_update_time() RETURNS OPAQUE AS
> 'BEGIN
>      NEW.updated := ''now'';
>      RAISE NOTICE ''Resetting %.updated to %'', TG_RELNAME, NEW.updated;
>      RETURN NEW;
> END;'
> LANGUAGE 'plpgsql';
>
> CREATE TABLE plpgsql_test (
>      id      SERIAL,
>      created TIMESTAMP NOT NULL DEFAULT now(),
>      updated TIMESTAMP NOT NULL DEFAULT now()
> );
>
> CREATE TRIGGER plpgsql_test_trigger
>     AFTER UPDATE ON plpgsql_test FOR EACH ROW
>     EXECUTE PROCEDURE set_update_time();

I think you want a before update trigger since you're
modifying NEW.