Обсуждение: Question about catching exception

Hello!

I have a question about catching exceptions.

If I write a plpgsql function like this

begin
   do stuff;
exception
 when X then
    ....
when Y then
  ...
end;

If the "do stuff" part can result in two different unique_violation
exception (having two unique constraints), how can I detect which one
was triggered?

Hello

you have to parse a sqlerrm variable

CREATE OR REPLACE FUNCTION public.test(a integer, b integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
begin
  insert into foo values(a,b);
  exception when  unique_violation then
    raise notice '% %', sqlerrm, sqlstate;
end;
$function$

postgres=# select test(4,2);
NOTICE:  duplicate key value violates unique constraint "foo_b_key" 23505
 test
──────

(1 row)

Time: 9.801 ms
postgres=# select test(3,2);
NOTICE:  duplicate key value violates unique constraint "foo_a_key" 23505
 test
──────

(1 row)

Time: 17.167 ms

regards

Pavel Stehule



> If the "do stuff" part can result in two different unique_violation
> exception (having two unique constraints), how can I detect which one
> was triggered?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

> Hello
>
> you have to parse a sqlerrm variable

That's one way to do that. Another - more complex but more correct in many
cases is using two separate blocks.

BEGIN
   ... do stuff involving constraint A
EXCEPTION
   WHEN unique_violation THEN ...
END;

BEGIN
   ... do stuff involving constraint B
EXCEPTION
   WHEN unique_violation THEN ...
END;

But that's not possible if there are two unique constraints involved in a
single SQL statement (e.g. inserting into a table with two unique
constraints).

regards
Tomas