insert rule instead oddity
От | Sim Zacks |
---|---|
Тема | insert rule instead oddity |
Дата | |
Msg-id | et64m5$37e$1@news.hub.org обсуждение исходный текст |
Ответы |
Re: insert rule instead oddity
|
Список | pgsql-general |
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)
В списке pgsql-general по дате отправления: