Postgres Pro
Downloads
Home   >   mailing lists

Thread: parsing SQLERRM ?

parsing SQLERRM ?

From
Date:
(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/only way to determine this ?
 
-dvs-
 

Re: parsing SQLERRM ?

From
"Albe Laurenz"
Date:
david.sahagian@emc.com wrote:
> (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/only way to
determine this ?

I think so.

Not that it is particularly nice, though.  It should be fairly robust to
search for the name of the constraint in the error message.

Yours,
Laurenz Albe

Re: parsing SQLERRM ?

From
Tom Lane
Date:
"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> david.sahagian@emc.com wrote:
>> In my PL/pgSQL stored functions,
>> I want to be able to distinguish which FK-constraint caused the
>> [foreign_key_violation] exception.
>> Is a "robust enough" parsing of SQLERRM actually the best/only way to
>> determine this ?

> I think so.

Yeah, at the moment.  There are plans to fix this, but it won't happen
before 9.3 at the earliest.

            regards, tom lane