Postgres Pro
Downloads
Home   >   mailing lists

Re: How to trap invalid enum input exception? - Mailing list pgsql-novice

From Leon Starr
Subject Re: How to trap invalid enum input exception?
Date
Msg-id 458E084A-386F-4967-A2A3-C49C5C945B6D@modelint.com
Whole thread Raw
In response to Re: How to trap invalid enum input exception?  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: How to trap invalid enum input exception?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-novice
Tree view
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
-----------------------------------------------------------------------------------------




pgsql-novice by date:

Previous
From: Chris Campbell
Date:
Subject: Re: Listing Schemas - Revisited
Next
From: Tom Lane
Date:
Subject: Re: How to trap invalid enum input exception?