select version()
"PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo
Linux 3.3.5-r1, ssp-3.3.2-3, pie-8.7.7.1)"
I am sure that I must have missed something here because I read the documentation and searched the
forums and it all seems fairly straightforward.
I am doing an Insert Rule with Instead that if the row already exists then it should update a field
instead of Inserting the row. This works great when the row exists, but when the row doesn't exist
it is doing both the insert and the update (in other words I'm getting double value in the field
that is supposed to be updated when the row is found.
I put the notice into the function just to ascertain that it was actually calling it after doing the
insert.
create or replace function func_rul_insertstock(v_stock int,v_pnid int,v_stocklocationid int)
returns void as
$$
begin
Update stock set stock=stock+v_stock where pnid=v_pnid and
coalesce(stocklocationid,-1)=coalesce(v_stocklocationid,-1);
raise notice 'Doing function';
return;
end;
$$ language 'plpgsql';
create or replace rule rul_insertstock as on insert
to stock where exists(select stockid from stock where pnid=new.pnid
and ownerid=new.ownerid and
coalesce(stocklocationid,-1)=coalesce(new.stocklocationid,-1))
Do Instead
select func_rul_insertstock(new.stock,new.pnid,new.stocklocationid);
insert into stock(partid,pnid,ownerid,stock,stocklocationid)
values(1036,9243,10,150,1)
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
