Обсуждение: Test text value as interval
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. -- Robert
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 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. Regards, Jeff Davis
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
Robert Fitzpatrick <lists@webtent.net> writes: > 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... SYNTAX_ERROR is for SQL-command syntax errors. What you're after is a data exception. Here's how to figure out what you want: in psql, provoke the error and find out the SQLSTATE number. regression=# \set VERBOSITY verbose regression=# select 'foo'::text::interval; ERROR: 22007: invalid input syntax for type interval: "foo" LOCATION: DateTimeParseError, datetime.c:3137 Now look up "22007" in the list of error codes http://www.postgresql.org/docs/8.2/static/errcodes-appendix.html and you'll find out it's "invalid_datetime_format". Looking at the list, there are some other codes like interval_field_overflow that you'll likely want to trap too. In fact, if this is the *only* operation within the exception block, maybe you should just do "when others", assuming that the only possible cause of an error is bogus input data. regards, tom lane