[GENERAL] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Test text value as interval
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Test text value as interval
Robert Fitzpatrick [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
