Re: Re: Date of creation and of change

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