Re: Create a function that updates the record with and timestamps

In response to Richard Sickler :
> > I am not sure if there is a very simple way of doing this?
> >
> > Or, do I need to create a function and a trigger to call the row and
> update
> > with new data and set the last_modified to current_date?
>
> Yes, that's the way, a TRIGGER on UPDATE for each row. I think, the doc
> contains an example.
>
>
> Andreas
>
>
> From a novice: I use    last_updated_at timestamp without time zone NOT NULL
> DEFAULT now()

This works only for INSERT, but not for UPDATE.

test=# create table richard (id int, last_updated_at timestamp without time zone NOT NULL DEFAULT NOW());
CREATE TABLE
test=# insert into richard (id) values (1);
INSERT 0 1
test=# select * from richard ;
id | last_updated_at
----+----------------------------
1 | 2010-03-23 06:54:28.656668
(1 row)

test=# select now();
now
-------------------------------
2010-03-23 06:54:42.443224+01
(1 row)

test=# UPDATE richard set id=2 where id=1;
UPDATE 1
test=# select * from richard ;
id | last_updated_at
----+----------------------------
2 | 2010-03-23 06:54:28.656668
(1 row)

As you can see, the last_updated_at isn't up-to-date ;-)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99