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; ...
