trigger question

From: mikeo <mikeo(at)spectrumtelecorp(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: trigger question
Date: 2000-06-27 12:31:37
Message-ID: 3.0.1.32.20000627083137.009497f0@pop.spectrumtelecorp.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi,
i've created a function as follows:

drop function rates_hist_function();
CREATE function rates_hist_function()
returns opaque
as 'BEGIN
if ( old.rt_valid <> ''P'' or new.rt_valid not in
(''Y'',''N''))
then
new.rt_timestamp = now();
insert into rates_hist values (
new.cut_id,new.ct_key,new.rtm_id,new.rt_sell_factor,

new.rt_sell_msg_cost,new.rt_sell_init_sec,new.rt_sell_init_cost,

new.rt_sell_addl_sec,new.rt_sell_addl_cost,new.rt_buy_factor,

new.rt_buy_msg_cost,new.rt_buy_init_sec,new.rt_buy_init_cost,

new.rt_buy_addl_sec,new.rt_buy_addl_cost,new.rt_valid,new.rse_id,
new.wu_id,new.rt_timestamp, new.rt_usoc_def_factor
);
end if;
return new;
END;'
language 'plpgsql';

and i call it from this trigger:

drop TRIGGER rates_hist_trigger on rates;
CREATE TRIGGER rates_hist_trigger
after insert or update on rates
for each row
execute procedure rates_hist_function();

when i attempt to insert a row into the rates table using this statement:

insert into rates(cut_id,ct_key,rt_valid,...,rt_timestamp,rt_usoc_def_factor)
values ('mikeo',123456,'x',...,now(),1.35);

i get this error:
ERROR: record old is unassigned yet

since this trigger is for both insert or update, why does it expect the "OLD"
value to already exist, as it would not for insert? second, is there a way
to tell a function that it's inserting or updating, as in oracle's
"if updating..."?

thanks,
mikeo

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Poul L. Christiansen 2000-06-27 12:41:40 Re: How to dump from Postgre
Previous Message Carsten Huettl 2000-06-27 11:50:06 Re: FATAL 1: Database dbname=template1 does not exist in pg_database