Обсуждение: How to trap invalid enum input exception?

Hi all, I have the following defined in my database:

CREATE TYPE side AS ENUM ('right', 'left');

And I've defined a function that with a parameter of this type.

CREATE OR REPLACE FUNCTION test_input ( p_side side ) ...

Now I test the function with an invalid value:

db=# select test_input('blue');
ERROR:  invalid input value for enum side: "blue"

I want to trap the exception in my function
EXCEPTION
    WHEN ??? THEN
        RAISE EXCEPTION ...

I tried the obvious INVALID_INPUT_VALUE with no success.

Two questions:

1) What is the actual name of the condition (and, yes, I looked through appendix A trying to figure it out) for invalid
enuminput? 

2) Is there a way to retrieve the error code so that I don't have to guess at the condition name?  (I've been playing
thisgame a lot with other exceptions and I don't understand WHY the condition or code isn't provided when an untrapped
erroroccurs)  Clearly, I'm missing something! 

As always, help or informative redirects muchly appreciated!

- Leon

Leon Starr <leon_starr@modelint.com> writes:
> db=# select test_input('blue');
> ERROR:  invalid input value for enum side: "blue"

> 1) What is the actual name of the condition (and, yes, I looked through appendix A trying to figure it out) for
invalidenum input? 

[ looks at code... ] Try INVALID_TEXT_REPRESENTATION.

> 2) Is there a way to retrieve the error code so that I don't have to guess at the condition name?  (I've been playing
thisgame a lot with other exceptions and I don't understand WHY the condition or code isn't provided when an untrapped
erroroccurs)  Clearly, I'm missing something! 

In psql, you can do this:

regression=# CREATE TYPE side AS ENUM ('right', 'left');
CREATE TYPE
regression=# select 'blue'::side;
ERROR:  invalid input value for enum side: "blue"
LINE 1: select 'blue'::side;
               ^
regression=# \set VERBOSITY verbose
regression=# select 'blue'::side;
ERROR:  22P02: invalid input value for enum side: "blue"
LINE 1: select 'blue'::side;
               ^
LOCATION:  enum_in, enum.c:56

and after that, you can either look up the SQLSTATE 22P02 in appendix A,
or consult the source code in enum_in().  If you're not using psql, the
same information should be available through the client API you're using
--- feel free to complain to its authors if not.

            regards, tom lane

Leon Starr <leon_starr@modelint.com> writes:
> I have tried catching following with no success:
>     INVALID_TEXT_REPRESENTATION
>     SQLSTATE '22P02'
>     SQLSTATE '22000'
>     DATA_EXCEPTION
>     OTHERS

> (lest we think I am completely incompetent ;), I have had success
> catching all manner of FOREIGN_KEY_VIOLATION, UNIQUE_VIOLATION, CHECK_VIOLATION, NO_DATA_FOUND, etc. in other
functions.  So something is very different about this particular exception!) 

> It seems to me that it is not a normal exception and evades the exception clause.  Any thoughts on where to go from
here?

Hmm, maybe the error is not being thrown when/where you think it is?
Coercions of constants, in particular, happen very early and might
possibly need some contortions to catch.  Could we see an exact example
of what's not working for you?

            regards, tom lane

Leon Starr <leon_starr@modelint.com> writes:
> I've trimmed my example down to the bare minimum in hopes of solving the problem myself, but no luck.  Here's the
exactcode and console session: 

> create type side as enum ('right', 'left');

> create or replace function testinput(
>     p_units_align    side
> ) returns void as
> ....

> contracts=# select * from testinput('blue');
> ERROR:  22P02: invalid input value for enum side: "blue"
> LINE 1: select * from testinput('blue');
>                                 ^
> LOCATION:  enum_in, enum.c:57

Well, the point here is that the system has to convert 'blue' to a value
of type "side" before it ever invokes your function.  So there's no hope
of trapping that failure inside the function.

If you really want to do things this way, you can declare the function
as taking a text string, and cast from text to "side" within the
function's exception-trapping block.

            regards, tom lane