How to trap invalid enum input exception?

From: Leon Starr <leon_starr(at)modelint(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: How to trap invalid enum input exception?
Date: 2010-08-16 02:11:05
Message-ID: D156D49E-A668-4775-9231-F7AF94190654@modelint.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2010-08-16 03:12:02 Re: How to trap invalid enum input exception?
Previous Message Rikard Bosnjakovic 2010-08-15 08:19:54 Re: Help with trigger