Re: 7.2.1 PL/pgSQL issue

Try changing your trigger to

CREATE TRIGGER plpgsql_test_trigger
BEFORE UPDATE ON plpgsql_test FOR EACH ROW
EXECUTE PROCEDURE set_update_time();

http://www.pgexplorer.com

----- Original Message -----
From: "Ed Loehr" <pggeneral(at)bluepolka(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Friday, April 12, 2002 8:23 AM
Subject: [GENERAL] 7.2.1 PL/pgSQL issue

> 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();
>
> -- Insert 1 and note the timestamps...
> INSERT INTO plpgsql_test (id) VALUES (0);
> SELECT * FROM plpgsql_test;
>
> -- Wait a few seconds, then...
>
> UPDATE plpgsql_test SET id = (SELECT nextval('plpgsql_test_id_seq'));
>
> -- This should show an updated value for
> -- plpgsql_test.updated, but does not on my system...
> SELECT * FROM plpgsql_test;
>
>
> -- Clean-up...
> DROP FUNCTION set_update_time();
> DROP SEQUENCE plpgsql_test_id_seq;
> DROP TABLE plpgsql_test;
>
> template1=# select version();
> version
> -------------------------------------------------------------
> PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
> (1 row)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>