parsing SQLERRM ?
(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 ?
-dvs-
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