insert rule instead oddity

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

Предыдущее
От: "Guillaume Bog"
Дата:
Сообщение: Re: finding a column by name in psql
Следующее
От: Martijn van Oosterhout
Дата:
Сообщение: Re: insert rule instead oddity