Re: Test text value as interval

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