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