Postgresql – Insert on conflict return false otherwise true in single query in stored function

postgresql

At now I use code like that

create function something()
returns boolean
as 
$$
begin
   if exists (select 1 from some_table where some_cond) then
       return false;
   end if;

   insert into some_table (value)
   values (2)
   -- on conflict do !!! return false
   returning true;
end
$$
language 'plpgsql';

Is there a way to return value from on conflict section without defining a variable or temp table in one query?

Best Answer

You can handle exceptions.

create table test (id int primary key, data text);

insert into test values (1,'a'), (2,'b'), (3, 'c');

create or replace function test_error(_id int, _data text)
returns boolean as $res$
declare res boolean;
begin
    res = true;
    begin
        -- uncomment to check other exception 
        --select 1 / 0;
        insert into test values (_id, _data);
    exception
    when unique_violation then
        res = false;
    when others then
        raise exception 'Other exception';
    end;
    return res;
end;
$res$ language plpgsql;

select test_error(4, 'd');

| test_error |
| :--------- |
| t          |

If you try to insert a duplicate key:

select test_error(1, 'n');

| test_error |
| :--------- |
| f          |

db<>fiddle here

Related Question