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@bluepolka.net>
To: <pgsql-general@postgresql.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@postgresql.org
>