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
beingupdated 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
PostGrestables 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