Ian Pilcher <arequipeno@gmail.com> wrote:
> I can't be the first person (or even the 10,000th) to want to define my
> own SQLSTATE codes when raising errors in a stored procedure. I've
> just tested doing so in a PL/pgSQL function access via JDBC, and I had
> no problem retrieving the non-standard state from the SQLException. (I
> used 'WWWWW' and 'ZZZZZ' as my tests.)
>
> Is there anything like a standard range -- formal or otherwise -- for
> such codes? A best practice? A general consensus? A half-painted
> bike shed?
There is this in the SQL standard. According to that, SQLSTATE
values with 0-4 or A-H in both the first and third positions are
reserved for values defined by standards. All others are available
for "implementation-specified" exception conditions. As far as I
know, the PostgreSQL community has claimed SQLSTATE values with P0
or XX in the start of a SQLSTATE or P in the third character. To
allow for future expansion by the PostgreSQL community it might be
wise to stay away from any SQLSTATE starting with P for your
application use.
-Kevin