Lists: | pgsql-general |
---|
From: | A B <gentosaker(at)gmail(dot)com> |
---|---|
To: | postgresql Forums <pgsql-general(at)postgresql(dot)org> |
Subject: | Question about catching exception |
Date: | 2010-11-26 09:39:55 |
Message-ID: | AANLkTikhV_jiroxUNT+WbVv4myCxrasFajO0TfdtdEK+@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | A B <gentosaker(at)gmail(dot)com> |
Cc: | postgresql Forums <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Question about catching exception |
Date: | 2010-11-26 09:52:48 |
Message-ID: | AANLkTimjJw_-tk4YDLTPk11srnXzqZkT_dQOsPHzKDA=@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From: | tv(at)fuzzy(dot)cz |
---|---|
To: | "A B" <gentosaker(at)gmail(dot)com> |
Cc: | "postgresql Forums" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Question about catching exception |
Date: | 2010-11-26 10:04:03 |
Message-ID: | decfe71cc7af426aa9d0a4e80559a354.squirrel@sq.gransy.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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