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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
