From: | Jason Godden <jasongodden(at)optushome(dot)com(dot)au> |
---|---|
To: | Alessandro GARDICH <gremlin(at)gremlin(dot)it>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: automatic update |
Date: | 2003-09-02 09:34:26 |
Message-ID: | 200309021934.26949.jasongodden@optushome.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 ...
From | Date | Subject | |
---|---|---|---|
Next Message | Bo Lorentsen | 2003-09-02 09:49:16 | Re: Commercial postgresql |
Previous Message | Alessandro GARDICH | 2003-09-02 08:56:48 | automatic update |