How to determine PostgreSQL Error Codes from error message-CodePudding

In a PL/pgSQL function, I want to trap an error. For example, when I convert text to a number, I get an error (I run it in psql, but get the same error in the Postico GUI client):

select 'a'::numeric;

ERROR:  invalid input syntax for type numeric: "a"
LINE 1: select 'a'::numeric;
               ^

To trap this error, I made an EXCEPTION-clause like this:

CREATE OR REPLACE FUNCTION public.to_number(input text) RETURNS numeric
    AS $$
BEGIN
    RETURN input::numeric;
EXCEPTION
    WHEN OTHERS THEN 
        RETURN NULL;
END
$$
    LANGUAGE plpgsql
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
;

However, I don't like the condition WHEN OTHERS.

How do I map the error message ERROR: invalid input syntax for type numeric: "a" to one mentioned in the Appendix A. PostgreSQL Error Codes?

I want to catch the conversion error when converting to numeric and no other conditions (as the above function is a simplified one).

I have the feeling I'm missing something, but what?

CodePudding user response：

When you get the error in psql, run

\errverbose

and you'll get information like

ERROR:  22P02: invalid input syntax for type numeric: "a"
LINE 1: select 'a'::numeric;
               ^
LOCATION:  set_var_from_str, numeric.c:6856

22P02 is the SQLSTATE, and Appendix A of the documentation will tell you that that is invalid_text_representation.