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
