6

I'm writing a collection of stored functions in Postgres 11.5, and want to RAISE EXCEPTION when various pre-conditions are not met. For example, a null or empty string with a string is required, or an integer parameter that is out of range, etc.

I can RAISE EXCEPTION and provide details, hints, and a message...but what range should I use for the error code? I've checked the docs, but didn't find any guidance here:

https://www.postgresql.org/docs/11/errcodes-appendix.html

I searched on StackOverflow, found a similar question from years ago...but not a clear answer.

Is there some block or prefix that's safe or conventional to use for custom error codes coming back from stored functions/procedures?

0

1 Answer 1

6

The SQL standard gives you a guideline:

The character string value returned in an SQLSTATE parameter comprises a 2-character class value followed by a 3-character subclass value, each with an implementation-defined character set that has a one-octet character encoding form and is restricted to <digit>s and <simple Latin upper case letter>s. Table 33, “SQLSTATE class and subclass values”, specifies the class value for each condition and the subclass value or values for each class value.

Class values that begin with one of the <digit>s '0', '1', '2', '3', or '4' or one of the <simple Latin upper case letter>s 'A', 'B', 'C', 'D', 'E', 'F', 'G', or 'H' are returned only for conditions defined in ISO/IEC 9075 or in any other International Standard. The range of such class values is called standard-defined classes. Some such class codes are reserved for use by specific International Standards, as specified elsewhere in this Clause. Subclass values associated with such classes that also begin with one of those 13 characters are returned only for conditions defined in ISO/IEC 9075 or some other International Standard. The range of such subclass values is called standard-defined subclasses. Subclass values associated with such classes that begin with one of the <digit>s '5', '6', '7', '8', or '9' or one of the <simple Latin upper case letter>s 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-defined conditions and are called implementation-defined subclasses.

Class values that begin with one of the <digit>s '5', '6', '7', '8', or '9' or one of the <simple Latin upper case letter>s 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-defined exception conditions and are called implementation-defined classes. All subclass values except '000', which means no subclass, associated with such classes are reserved for implementation-defined conditions and are called implementation-defined subclasses. An implementation-defined completion condition shall be indicated by returning an implementation-defined subclass in conjunction with one of the classes successful completion, warning, or no data.

If your exception matches one of the SQLSTATEs in Appendix A of the PostgreSQL documentation, simply use that SQLSTATE.

If you need to use your own SQLSTATE, let it begin with any of 5 to 9 or I to Z (but avoid the SQLSTATEs used by PostgreSQL).

If you need to define a custom warning, use an SQLSTATE that starts with 01 and whose third character is any of 5 to 9 or I to Z.

1
  • 1
    "let it begin with any of 5 to 9 or I to Z." - notice that Postgres itself does use P0 and XX already
    – Bergi
    Apr 11, 2023 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.