automatic update

[Alessandro GARDICH]

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 <gr*****@gremlin.it>
gremlin.it

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Jason Godden]

A trigger is the right way. Rules rewrite a statement whereas triggers are at
row level.

so in plpgsql:

create or replace function trig_param_ins_upd() returns trigger as '
begin
new.ts = current_timestamp;
return new;
end;' language 'plpgsql';

create trigger trig_param_ins_upd before insert or update on param
for each row execute procedure trig_param_ins_upd();

now whenever a new row is insert or a row is updated the trigger will ensure
that ts always contains the current timestamp.

Rgds,

Jason

On Tue, 2 Sep 2003 06:56 pm, 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 ...

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 ...

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org