automatic update
Hi to all ...
I'm looking to a way to auto update some fields of a row when other
fileds are updated.
the table structure are simple,
CREATE TABLE param (
id int4 PRIMARY KEY,
val int4,
ts timestam(3) DEFAULT CURRENT_TIMESTAMP
);
so when a new entry are insert ts areautomatically update,
but i would make the same on update ...
i would that on
UDPATE param SET val=100 WHERE id=1;
also ts field have to be updated to CURRENT_TIMESTAMP
I try with a RULE but obtain only a loop, seem RULE aren't good to make
such things, modify a statment on the same table it's related :(
i look for a trigger but I suppose the same problem arise ...
how i can solve the problem ???
mhhh does i have to have a VIEW of parm ... called param2 without the ts
field and make a rule on param2 that update param.ts ???
seem a bit tricky :( ...
thanks in advance ...
--
Alessandro GARDICH <gremlin@gremlin.it>
gremlin.it
On Tuesday 02 September 2003 09:34, Alessandro GARDICH wrote:
Hi to all ...
I'm looking to a way to auto update some fields of a row when other
fileds are updated.the table structure are simple,
CREATE TABLE param (
id int4 PRIMARY KEY,
val int4,
ts timestam(3) DEFAULT CURRENT_TIMESTAMP
);so when a new entry are insert ts areautomatically update,
but i would make the same on update ...
[snip]
I try with a RULE but obtain only a loop, seem RULE aren't good to make
such things, modify a statment on the same table it's related :(i look for a trigger but I suppose the same problem arise ...
No - a trigger is what you want. A rule is basically a macro - it transforms
the query, so you can end up with a loop.
If you use a BEFORE UPDATE trigger, you could update NEW.ts to always be up to
date.
Look for my "PostgreSQL Notes" on http://techdocs.postgresql.org/ - there's an
example of this in there somewhere (a bit out of date, but still should be
valid). Probably also looking for the "PostgreSQL cookbook" on the same site.
--
Richard Huxton
Archonet Ltd