Обсуждение: 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)

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.

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
>