Skip site navigation (1) Skip section navigation (2)

Re: How to trap invalid enum input exception?

From: Leon Starr <leon_starr(at)modelint(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to trap invalid enum input exception?
Date: 2010-08-16 17:13:33
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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 
		when SQLSTATE '22P02' then
			raise 'Got it';
			raise 'Got it';
		when OTHERS then
			raise 'Got it';
language plpgsql;

Then, in the psql console:
contracts=# \i testinput.sql
contracts=# select * from testinput('right');
(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(at)modelint(dot)com> writes:
>> I have tried catching following with no success:
>> 	SQLSTATE '22P02'
>> 	SQLSTATE '22000'
>> (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(at)postgresql(dot)org)
> To make changes to your subscription:

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

In response to


pgsql-novice by date

Next:From: Tom LaneDate: 2010-08-16 17:40:02
Subject: Re: How to trap invalid enum input exception?
Previous:From: Chris CampbellDate: 2010-08-16 16:15:48
Subject: Re: Listing Schemas - Revisited

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group