From: Jamie Lawrence <jal@jal.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Auto-update a field when record is changed
Date: Sat, 23 Aug 2003 01:16:53 -0400
Message-ID: <20030823051653.GA6038@clueinc.net> (raw)
In-Reply-To: <20030822215217.L6822-100000@megazone.bigpanda.com>
References: <200308222332.47526.sfbarbee@yahoo.com>
<20030822215217.L6822-100000@megazone.bigpanda.com>
> On Fri, 22 Aug 2003, Stuart wrote:
>
> > Folks,
> >
> > I was wandering if there was a feasible way to automatically update a field
> > in a table, say 'revision_date' in a record whenever any other field in the
> > record is changed. My attempts to use a trigger caused repeating loops and
> > bombed with error. I would like to be able to update such a field
> > automatically anytime a record was updated. Any help would be appreciated.
Unless I'm misunderstanding you, this is really easy. Here's what
I use in nearly every database I build:
create or replace function timestamp_fn() returns opaque as '
begin
NEW.moddate = now();
return NEW;
end
' language 'plpgsql';
create table blah (
...
createdate timestamp default now(),
moddate timestamp,
create trigger blah_timestamp_tr before insert or update on blah
for each row execute procedure timestamp_fn();
Make the obvious changes for only doing this on updates.
Or am I misunderstanding your goal?
-j
--
Jamie Lawrence jal@jal.org
"One of the great things about books is that sometimes there
are some fantastic pictures."
- George H. W. Bush
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jal@jal.org
Subject: Re: Auto-update a field when record is changed
In-Reply-To: <20030823051653.GA6038@clueinc.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox