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
