Received: from mx2.imaginet.fr (artemis.imaginet.fr [195.68.75.24]) by hub.org (8.10.1/8.10.1) with ESMTP id e7PEWuI61796 for ; Fri, 25 Aug 2000 10:32:56 -0400 (EDT) Received: from lexbase.net (access-34.125.rev.fr.colt.net [195.68.125.34] (may be forged)) by mx2.imaginet.fr (8.9.3/8.8.8) with ESMTP id QAA21083; Fri, 25 Aug 2000 16:31:15 +0200 (MET DST) Message-ID: <39A686CC.361904E3@lexbase.net> Date: Fri, 25 Aug 2000 16:46:36 +0200 From: hlefebvre Organization: LEXBASE X-Mailer: Mozilla 4.73 [en] (WinNT; U) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: Andreas Tille , PostgreSQL SQL Subject: Re: Re: Date of creation and of change References: <2205.967212888@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/282 Tom Lane wrote: > > Andreas Tille writes: > >> NEW.ChangedAt := timestamp(''now''); > > > This avoids the error message, but doesn't have any effect to the value > > of ChangedAt. It just remains the same as CreatedAt :-(. > > I think you are getting burnt by premature constant folding --- see > nearby discussion of how to define a column default that gives the > time of insertion. You need to write this as > NEW.ChangedAt := now(); > to prevent the system from reducing timestamp('now') to a constant > when the function is first executed. > > regards, tom lane yep you're right : aegir=# aegir=# drop table menu aegir-# ; DROP aegir=# drop function changed_at_timestamp() ; DROP aegir=# CREATE TABLE Menu ( aegir(# CreatedAt timestamp DEFAULT now(), aegir(# ChangedAt timestamp aegir(# ); CREATE aegir=# aegir=# aegir=# CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS ' aegir'# BEGIN aegir'# NEW.ChangedAt := now(); aegir'# RETURN NEW; aegir'# END; aegir'# ' LANGUAGE 'plpgsql'; CREATE aegir=# aegir=# aegir=# CREATE TRIGGER menu_changed_at_timestamp BEFORE INSERT OR UPDATE ON Men u aegir-# FOR EACH ROW EXECUTE PROCEDURE changed_at_timestamp(); CREATE aegir=# aegir=# insert into menu(createdat) values(null); INSERT 27700 1 aegir=# select * from menu; createdat | changedat -----------+------------------------ | 2000-08-25 16:29:28+02 (1 row) aegir=# insert into menu(createdat) values(null); INSERT 27701 1 aegir=# select * from menu; createdat | changedat -----------+------------------------ | 2000-08-25 16:29:28+02 | 2000-08-25 16:30:53+02 (2 rows) aegir=# update menu set createdat = now(); UPDATE 2 aegir=# select * from menu; createdat | changedat ------------------------+------------------------ 2000-08-25 16:31:24+02 | 2000-08-25 16:31:24+02 2000-08-25 16:31:24+02 | 2000-08-25 16:31:24+02 (2 rows)