From: | "Joel Burton" <joel(at)joelburton(dot)com> |
---|---|
To: | "Rory Campbell-Lange" <rory(at)campbell-lange(dot)net>, "Postgresql Novice List" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: auto update dates |
Date: | 2002-05-23 14:23:51 |
Message-ID: | JGEPJNMCKODMDHGOBKDNOECICPAA.joel@joelburton.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)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(at)campbell-lange(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua b. Jore | 2002-05-23 14:27:03 | Re: auto update dates |
Previous Message | Rory Campbell-Lange | 2002-05-23 13:02:51 | auto update dates |