Thread: Range for user-defined SQLSTATE codes
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? Thanks! -- ======================================================================== Ian Pilcher arequipeno@gmail.com Sometimes there's nothing left to do but crash and burn...or die trying. ======================================================================== -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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
Kevin Grittner <kgrittn@ymail.com> writes:
> Ian Pilcher <arequipeno@gmail.com> wrote:
>> 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.
In addition to what the spec says, it's worth looking into
src/backend/utils/errcodes.txt, where you'll note that we have borrowed
some codes beginning with '5' from DB2, and also stated that codes
beginning with 'Y' are reserved for client-side code such as ecpg.
So I'd stay away from 5, P, X, and Y as first characters of private
SQLSTATEs (or third characters of private codes within a spec-defined
class, if that's what you want). Other than that, go nuts.
regards, tom lane