> How do people implement insert or upate ( otherwise known as upsert ) behaviour in postgres i.e. insert a row if it's
keydoes not exist in the database else update the existing row?
>
> I tried using an insert rule to delete any existing rows first then insert however this leads to infinitely recursive
rules( which postgres properly rejects. )
>
> I'm guesisng that the most sensible approach would be a stored proc/function?
I've done it with an on insert trigger. The where clause contains the
values that make this row unique. If it finds another row with the same
fields, it doe san update instead of the insert:
CREATE OR REPLACE FUNCTION stock.trg_beforeinsertstock()
RETURNS "trigger" AS
$BODY$
declare
v_stockid int;
begin
select stockid into v_stockid from stock where pnid=new.pnid
and ownerid=new.ownerid and
coalesce(stocklocationid,-1)=coalesce(new.stocklocationid,-1)
and coalesce(batchid,-1)=coalesce(new.batchid,-1);
if v_stockid is not null then
Update stock set stock=stock+new.stock where stockid=v_stockid;
return null;
else
return new;
end if;
end;
$BODY$
LANGUAGE plpgsql VOLATILE;
