pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAuto-update a field when record is changed
3+ messages / 3 participants
[nested] [flat]
* Auto-update a field when record is changed
@ 2003-08-23 03:32 Stuart <sfbarbee@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Stuart @ 2003-08-23 03:32 UTC (permalink / raw)
To: pgsql-sql
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.
Thanks,
Stuart
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Auto-update a field when record is changed
@ 2003-08-23 04:53 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Stuart <sfbarbee@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2003-08-23 04:53 UTC (permalink / raw)
To: Stuart <sfbarbee@yahoo.com>; +Cc: pgsql-sql
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.
That seems like a job for a before trigger and changing the to be
update/inerted record before the action occurs in general. Don't
use an update statement but change the record variables given as the NEW
row.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Auto-update a field when record is changed
@ 2003-08-23 05:16 Jamie Lawrence <jal@jal.org>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jamie Lawrence @ 2003-08-23 05:16 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-08-23 05:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-23 03:32 Auto-update a field when record is changed Stuart <sfbarbee@yahoo.com>
2003-08-23 04:53 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-23 05:16 ` Jamie Lawrence <jal@jal.org>
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