Re: auto update dates

create table foo (id int, ts timestamp default current_timestamp);

create function update_ts () returns opaque as
  'begin
     new.ts = current_timestamp;
     return new;
   end' language plpgsql;

create trigger update_foo before update n foo for each row execute procedyre
update_ts();

and then create the same trigger for your other tables. you can re-use the
function.


Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Rory
> Campbell-Lange
> Sent: Thursday, May 23, 2002 9:03 AM
> To: Postgresql Novice List
> Subject: [NOVICE] auto update dates
>
>
> Sorry - I seem to be bombarding the list. I AM reading my copy of Bruce
> Momjian's book quite closely, I promise!
>
> I'm trying to make a rule to automatically update the time, date and
> timestamp 'modified' fields when a row is updated.
>
> This is what I have tried:
>
>     create rule ideas_insert_datetime_mod as on update to ideas
>     do
>         update ideas
>         set d_modified = CURRENT_DATE,
>         t_modified = CURRENT_TIME,
>         ts_modified = CURRENT_TIMESTAMP
>
> This creates a cycle condition, and the rule does not operate.
>
> Do I instead need to write a function and then trigger it after each
> update action? I have 3 tables each with similar columns, with the same
> column names, which I wish to update in the same way.
>
> Thanks for any help.
> Rory
> --
> Rory Campbell-Lange
> <rory@campbell-lange.net>
> <www.campbell-lange.net>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>