Re: Re: Date of creation and of change

Tom Lane wrote:
> 
> Andreas Tille <tillea@rki.de> 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+022000-08-25 16:31:24+02
|2000-08-25 16:31:24+02
 
(2 rows)