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 enum input?

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!

As always, help or informative redirects muchly appreciated!

- Leon

Search Discussions

  • Tom Lane at Aug 16, 2010 at 3:12 am

    Leon Starr 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
  • Leon Starr at Aug 16, 2010 at 3:54 am
    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

    leon_starr@modelint.com
    www.modelint.com

    www.linkedin.com/in/modelint

    912 Cole Street, Suite 161
    San Francisco, CA 94117
    -----------------------------------------------------------------------------------------
  • Tom Lane at Aug 16, 2010 at 3:58 am

    Leon Starr 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 at Aug 16, 2010 at 5:15 pm
    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

    leon_starr@modelint.com
    www.modelint.com

    www.linkedin.com/in/modelint

    912 Cole Street, Suite 161
    San Francisco, CA 94117
    -----------------------------------------------------------------------------------------
  • Tom Lane at Aug 16, 2010 at 5:40 pm

    Leon Starr 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
  • Leon Starr at Aug 16, 2010 at 6:30 pm
    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

    leon_starr@modelint.com
    www.modelint.com

    www.linkedin.com/in/modelint

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedAug 16, '10 at 2:11a
activeAug 16, '10 at 6:30p
posts7
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Leon Starr: 4 posts Tom Lane: 3 posts

People

Translate

site design / logo © 2022 Grokbase