## mark a section (Learn more)

create function change_region() returns trigger as $change_region$ declare delta_price real; old_nation integer; new_nation integer; old_region record; new_region record; begin old_nation := OLD.s_nationkey; new_nationkey := NEW.s_nationkey; select into old_region n_regionkey from nation where n_nationkey=old_nation; select into new_region n_regionkey from nation where n_nationkey=new_nation; if (old_region.n_regionkey=1) then if (new_region.n_regionkey=2) then delta_price:=0.8; else if (new_region.n_regionkey=3) then delta_price:=1.05; end if; else if (old_region.n_regionkey=2) then if (new_region.n_regionkey=1) then delta_price:=1.2; else if (new_region.n_regionkey=3) then delta_price:=1.1; end if; else if (old_region.n_regionkey=3) then if (new_region.n_regionkey=1) then delta_price:=0.8; else if (new_region.n_regionkey=2) then delta_price:=0.9; end if; end if; update partsupp set ps_supplycost=ps_supplycost*delta_price where ps_suppkey=NEW.s_suppkey; return null; end; $change_region* language plpgsql;

This paste will be private.