I am trying to write a trigger and a function that will update the pricing table. The trigger must shoot only when a new value containing 'regular' in offer column is inserted/updated:
drop trigger if exists reduced_offer on pricing;
drop function if exists offer_f();
create or replace function offer_f() returns trigger as $$
begin
if (new.title, new.publisher,new.offer) in --the offer already EXISTS
(select title, publisher, offer
from pricing)
then
raise notice 'exists';
update pricing
set price=new.price*0.9
where title=new.title and publisher=new.publisher and period=new.period;
else --the offer does NOT EXIST
raise notice 'not exists';
insert into pricing values
(new.title, new.publisher, 'renew', new.period, new.price*0.9);
end if;
return null;
end $$ language plpgsql;
create trigger reduced_offer
after insert or update
on pricing
for each row
when (new.offer='regular')
execute procedure offer_f();
The problem: When I run this query:
insert into pricing values ('Nature', 'UCLA', 'regular', 9900, 20);
when I know for sure that this entry was not in pricing table, I get stack overflow error and this ENDLESS output (more than 20000 lines):
NOTICE: exists
NOTICE: exists
CONTEXT: SQL statement "update pricing
set price=new.price*0.9 where title=new.title and publisher=new.publisher andperiod=new.period
and offer='regular'"
PL/pgSQL function offer_f() line 8 at SQL statement
NOTICE: exists
CONTEXT: SQL statement "update pricing
set price=new.price*0.9 where title=new.title and publisher=new.publisher andperiod=new.period
AND SO ON....
any idea what can cause this overflow?