parsing the SQLERRM string

From: <david(dot)sahagian(at)emc(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: parsing the SQLERRM string
Date: 2012-06-11 19:30:06
Message-ID: F3CBFBA88397EA498B22A05FFA9EC49D8A0269C4@MX22A.corp.emc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2012-06-11 19:47:36 Re: Question about load balance
Previous Message Condor 2012-06-11 19:17:20 Re: Question about load balance