Re: Test text value as interval

On Thu, 2008-02-07 at 16:58 -0800, Jeff Davis wrote:
> On Thu, 2008-02-07 at 19:37 -0500, Robert Fitzpatrick wrote:
> > Been searching for a way to do this, but haven't found what I was hoping
> > to find. Is there any way in pl/pgsql to test a text value to see if it
> > would be a valid interval without having to try and store in a field? In
> > a trigger, I'd like to test a NEW text type field. Right now, I have
> > just the following to generate an error...
> >
> > test := NEW.textfield::interval;
> >
> > I'd like to test the field and RAISE EXCEPTION if not valid interval.
>
> Trap the error and do what you want with it:
>
> http://www.postgresql.org/docs/8.3/static/plpgsql-control-
> structures.html#PLPGSQL-ERROR-TRAPPING
>

Yes, this looks like it might work, thanks! But not sure which condition
to look for or if I'm doing this correctly. I tried syntax_error
condition, but I'm still receiving the same cast error trying this in a
trigger function...

begin
begin
test := NEW.textfield::interval;
EXCEPTION
WHEN syntax_error THEN
RAISE NOTICE 'Invalid Duration';
return null;
end;
<snip other code>
return new;
end;

> Although: why do you want to generate your own error? It seems like it
> would probably be about the same as the error produced by the casting
> failure.

My application will display whatever I can return via raise exception,
hence, that's why I'm trying this. Looking for a way to translate to the
user...

update events set event_duration = '3ho' where event_id = 2;
ERROR: invalid input syntax for type interval: "3ho"

--
Robert