Postgresql function: get id of updated or inserted row -

i have function in postgresql database update row if exist or insert new 1 if doesn't exist:

create or replace function insert_or_update(val1 integer, val2 integer) returns void $$  declare  begin      update my_table set col2 = val2 col1 = val1;      if not found      insert my_table (col2) values ( val2 );      end if;  end;  $$ language 'plpgsql'; 

for it's working perfect want id of row if updated or inserted.
how can it?

your function declared returns void can't return anything.

assuming col1 primary key , defined serial, can this:

create or replace function insert_or_update(val1 integer, val2 integer)      returns int  $$  declare    l_id integer; begin      l_id := val1; -- initialize local variable.      update my_table         set col2 = val2      col1 = val1; -- !! important: assumes col1 unique !!      if not found         insert my_table (col2) values ( val2 )         returning col1 -- makes generated value available        l_id;     -- , stores in local variable     end if;       return l_id; -- return whichever used. end;  $$ language plpgsql; 

i changed 4 things compared function:

1. the function declared returns integer in order able return something
2. you need variable can store returned value insert statement
3. and generated value needs returned:
4. the language name identifier, must not quoted using single quotes.

---

if want distinguish between update or insert caller, initialize l_id null. in case function return null if update occurred , value otherwise.