Update and Insert cases in postgresql function

Question

I have a table tab with columns:

userid, usercode, value

I'm writing a plpgsql function that update/insert the table. In case userid & usercode exists I need to update value In case they don't I need to insert the new value

for example if table data is:

userid, usercode, value
  5       10        20 
  3       8         10

for function call:

addvalue(5, 10 , 40)
addvalue(1, 12 , 40.5)

The new data will be:

userid, usercode, value
  5       10        60 
  3       8         10
  1       12        40.5

This is My function body:

CREATE OR REPLACE FUNCTION addvalue(xuserid integer, xusercode Integer,xvalue Numeric)
RETURNS integer AS

$BODY$ 
begin

     insert into tab(userid,usercode,value) values(xuserid ,xusercode,xvalue);
     return 1
end;                
$BODY$
LANGUAGE plpgsql VOLATILE

What is the best way to find out if I need to do INSERT or UPDATE statments?

Should I do UPDATE and check if it returns 0 ( 0 rows updated) and in that case do INSERT? Or should I query tab table to see if userid = xuserid and usercode = xusercode?

You might be inserted in this: http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql. Or upgrade to postgres 9.5 and use `upsert`. — Gordon Linoff, Aug 11 '15 at 14:41
I'm on postgresql version 9.0 and can't upgrade further... not at this point anyway. — quack, Aug 11 '15 at 14:42
@quack - From Gordon's coment you ***could*** have searched the web for any of the many ways to do an `UPSERT` in PostgreSQL 9.0... http://www.the-art-of-web.com/sql/upsert/ — MatBailie, Aug 11 '15 at 14:56

score 1 · Accepted Answer · answered Aug 11 '15 at 14:55

begin
 IF xuserid not in (select distinct userid from tab) THEN
 insert into tab(userid,usercode,value) values(xuserid ,xusercode,xvalue);
 ELSE
 update tab
 set value = value + xvalue 
 where userid = xuserid and usercode = xusercode
 END IF;
 -- return 1
end;

Is this what you are trying to do?

Update and Insert cases in postgresql function

1 Answers1