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 18:30:08
Message-ID: 49E434A5-0755-4AFC-9667-922AC2BB8291@modelint.com (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)modelint(dot)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(at)postgresql(dot)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(at)modelint(dot)com
www.modelint.com

www.linkedin.com/in/modelint

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




In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2010-08-16 18:32:44
Subject: Re: Can I use check-constraint to set null?
Previous:From: Victor HugoDate: 2010-08-16 18:03:01
Subject: Re: Remote Connection to server: Connection Refused

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