On Mon, Sep 20, 2010 at 11:31 AM, Colin 't Hart <colinthart@gmail.com> wrote:
>> I think to_date is the wrong gadget to use here. You should probably be using the date input routine and trapping
anydata exception. e.g.:
>>
>> test_date := date_in(textout(some_text));
>>
>> In plpgsql you'd put that inside a begin/exception/end block that traps SQLSTATE '22000' which is the class covering
dataexceptions.
>
> So it's not possible using pure SQL unless one writes a function?
I think that is true.
> Are the is_<type> family of functions still desired?
I think it would be useful to have a way of testing whether a cast to
a given type will succeed. The biggest problem with the
exception-catching method is not that it requires writing a function
(which, IMHO, is no big deal) but that exception handling is pretty
slow and inefficient. You end up doing things like... write a regexp
to see whether the data is in approximately the right format and then
if it is try the cast inside an exception block. Yuck.
(On the other hand, whether the work that was done in 2002 is still
relevant to today's code is questionable. Things have changed a lot.)
> Also, where are the to_<type> conversions done?
I think maybe you are looking for the type input functions?
select typname, typinput::regprocedure from pg_type;
There are also some functions with names of the form to_<type>. You
can get a list of those with the following psql command:
\dfS to_*
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
