parsing the SQLERRM string

Started by Sahagian, Davidalmost 14 years ago1 messagesgeneral
Jump to latest
#1Sahagian, David
david.sahagian@emc.com

(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-