Обсуждение: parsing SQLERRM ?

david.sahagian@emc.com wrote:
> (version == 9.1)
>
> In my PL/pgSQL stored functions,
> I want to be able to distinguish which FK-constraint caused the
[foreign_key_violation] exception.
>   . . .
>   BEGIN
>     delete from MY_COOL_TABLE where id = 123 ;
>   EXCEPTION
>     WHEN foreign_key_violation THEN
>       CASE
>         WHEN (SQLERRM tells me it blew up because of FK X)  THEN . . .
;
>         WHEN (SQLERRM tells me it blew up because of FK Y)  THEN . . .
;
>         WHEN (SQLERRM tells me it blew up because of FK Z)  THEN . . .
;
>       END;
>     WHEN others THEN
>       raise;
>   END;
>   . . .
>
> Is a "robust enough" parsing of SQLERRM actually the best/only way to
determine this ?

I think so.

Not that it is particularly nice, though.  It should be fairly robust to
search for the name of the constraint in the error message.

Yours,
Laurenz Albe

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> david.sahagian@emc.com wrote:
>> In my PL/pgSQL stored functions,
>> I want to be able to distinguish which FK-constraint caused the
>> [foreign_key_violation] exception.
>> Is a "robust enough" parsing of SQLERRM actually the best/only way to
>> determine this ?

> I think so.

Yeah, at the moment.  There are plans to fix this, but it won't happen
before 9.3 at the earliest.

            regards, tom lane