Thread: How to trap invalid enum input exception?

How to trap invalid enum input exception?

From
Leon Starr
Date:
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




Re: How to trap invalid enum input exception?

From
Tom Lane
Date:
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

Re: How to trap invalid enum input exception?

From
Leon Starr
Date:
No success!

Actually, big thanks, Tom, for the psql verbosity option - that's actually a big help, however....

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?

(I suppose I could just live with the system generated error or do some sort of check constraint and jettison enums altogether)

- Leon



On Aug 15, 2010, at 8:12 PM, Tom Lane wrote:

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 invalid enum 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 this game a lot with other exceptions and I don't understand WHY the condition or code isn't provided when an untrapped error occurs)  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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

-----------------------------------------------------------------------------------------
Model Integration, LLC
Complex Application Requirements Analysis and Modeling
Expert UML/MDA Development, Training and Support

Leon Starr
Analyst / Model Engineer

+1 415 863 8649 office
+1 415 215 9086 cell



912 Cole Street, Suite 161
San Francisco, CA 94117
-----------------------------------------------------------------------------------------




Re: How to trap invalid enum input exception?

From
Tom Lane
Date:
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

Re: How to trap invalid enum input exception?

From
Leon Starr
Date:
I've trimmed my example down to the bare minimum in hopes of solving the problem myself, but no luck.  Here's the exact code and console session:

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

create or replace function testinput(
p_units_align side
) returns void as 
$$
begin
exception
when SQLSTATE '22P02' then
raise 'Got it';
when INVALID_TEXT_REPRESENTATION then
raise 'Got it';
when OTHERS then
raise 'Got it';
end
$$
language plpgsql;

Then, in the psql console:
contracts=# \i testinput.sql
CREATE FUNCTION
contracts=# select * from testinput('right');
 testinput 
-----------
 
(1 row)

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


What am I missing?

- Leon


On Aug 15, 2010, at 8:58 PM, Tom Lane wrote:

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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

-----------------------------------------------------------------------------------------
Model Integration, LLC
Complex Application Requirements Analysis and Modeling
Expert UML/MDA Development, Training and Support

Leon Starr
Analyst / Model Engineer

+1 415 863 8649 office
+1 415 215 9086 cell



912 Cole Street, Suite 161
San Francisco, CA 94117
-----------------------------------------------------------------------------------------




Re: How to trap invalid enum input exception?

From
Tom Lane
Date:
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

Re: How to trap invalid enum input exception?

From
Leon Starr
Date:
Ah!  That makes total sense.  I was starting down that line of thinking by entering my input as 'right'::side in the console, but still didn't figure it out.  Of course it has to be cast after input.  The world makes sense again.  Thanks!

- Leon

On Aug 16, 2010, at 10:40 AM, Tom Lane wrote:

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 exact code 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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

-----------------------------------------------------------------------------------------
Model Integration, LLC
Complex Application Requirements Analysis and Modeling
Expert UML/MDA Development, Training and Support

Leon Starr
Analyst / Model Engineer

+1 415 863 8649 office
+1 415 215 9086 cell



912 Cole Street, Suite 161
San Francisco, CA 94117
-----------------------------------------------------------------------------------------