pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: hlefebvre <hlefebvre@lexbase.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andreas Tille <tillea@rki.de>
Cc: PostgreSQL SQL <pgsql-sql@hub.org>
Subject: Re: Re: Date of creation and of change
Date: Fri, 25 Aug 2000 16:46:36 +0200
Message-ID: <39A686CC.361904E3@lexbase.net> (raw)
References: <Pine.LNX.4.21.0008251501070.716-100000@wr-linux02.rki.de>
	<2205.967212888@sss.pgh.pa.us>



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+02
 2000-08-25 16:31:24+02 | 2000-08-25 16:31:24+02
(2 rows)



view thread (15+ messages)  latest in thread

Message-ID: <39A686CC.361904E3@lexbase.net>
Permalink:  ../39A686CC.361904E3@lexbase.net/
Also on:    postgresql.org/message-id/39A686CC.361904E3@lexbase.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: hlefebvre@lexbase.net, tgl@sss.pgh.pa.us, tillea@rki.de, pgsql-sql@hub.org
  Subject: Re: Date of creation and of change
  In-Reply-To: <39A686CC.361904E3@lexbase.net>
  References: <Pine.LNX.4.21.0008251501070.716-100000@wr-linux02.rki.de> <2205.967212888@sss.pgh.pa.us> <39A686CC.361904E3@lexbase.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox