From: Dmitry Tkach <dmitry@openratings.com>
Subject: Re: timestamp
Date: Wed, 12 Feb 2003 12:55:22 -0500
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 69
Message-ID: <3E4A8A8A.5000103@openratings.com>
References: <20030212100915.296331a6.jaenecke@smaxs.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
To: Daniel Jaenecke <jaenecke@smaxs.de>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
In-Reply-To: <20030212100915.296331a6.jaenecke@smaxs.de>
To: pgsql-sql@postgresql.org

Inserting is easy:

create table timestamped
(
     id serial primary key,
     stuff text,
     stamp timestamp not null default now ()
);

insert (stuff) into timestamped values ('blah');


Updating is more tricky... One way is to use a rule:

create rule update_stamp as on update to timestamped where stamp is null do instead
update timestamped set stuff = new.stuff, timestamp=now() where id = new.id;

or

create rule update_replace as on update to timestamped do instead
(
    delete from timestamped where id=old.id;
    insert into timestamped values (new.*);
);

I like this one better, because it does not depend on any column names, except the it - so you can modify the original table,
but the rule will still work...

Another way is to create a trigger:

create function update_stamp () returns opaque as
'begin; new.stamp=now(); return new;end;'
language 'plpgsql';

create trigger update_stamp_tg before update on timestamped
for each row execute procedure update_stamp();

This is even better than the rule, because you can use that same function with any table that has a column, called 'stamp',
even if it doesn't have a primary key
You can also make the column name an argument,for complete flexibility but I don't know how to do that in plpgsql (I believe, it's possible though) -
can be fairly easily done in C if you are not afraid of writing C functions :-)

I hope, it helps...

Dima


Daniel Jaenecke wrote:
> Hi!
> 
> I am rather new to PostGreSQL, having mainly used MySQL until now.
> MySQL has a column type TIMESTAMP[1] which holds - as expected - a timestamp, but additionally this timestamp is being updated automatically on any UPDATE or INSERT operation.
> 
> Since this is a quite handy feature to keep track of latest changes I would like to create such a behavior for my PostGres tables too. But since I have no idea how to do it I would warml welcome any suggestion... :)
> 
> Thx 
> dj
> 
> [1]
> http://www.mysql.com/doc/en/DATETIME.html
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster