0

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?

1

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

2
  • I heard that exceptions are performance heavy things. And do you shadow another exceptions with "other exception" message?
    – Herrgott
    Jul 28 '19 at 23:50
  • Just to show how to get other exceptions than UNIQUE_VIOLATION
    – McNets
    Jul 29 '19 at 7:02

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .