(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 way to do this ?
If so, what assumptions can I make about the SQLERRM string ?
When I set lc_messages = en_US.UTF-8' or 'fr_FR.UTF-8' or 'zh_CN.UTF-8'
I get these messages:
INFO: 23503: update or delete on table "TBLONE" violates foreign key constraint "FK_XXX" on table "TBLTWO"
INFO: 23503: UPDATE ou DELETE sur la table « TBLONE » viole la contrainte de clé étrangère
« FK_XXX » de la table « TBLTWO »
信息: 23503: 在 "TBLONE" 上的更新或删除操作违反了在 "TBLTWO" 上的外键约束
"FK_XXX"
which seems to make any regex/parsing of SQLERRM real hard.
Is anybody doing this ?
Thanks,
-dvs-
