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)