Обсуждение: parsing the SQLERRM string

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

 

 

which seems to make any regex/parsing of SQLERRM real hard.

 

Is anybody doing this ?

 

Thanks,

-dvs-