On Mon, Sep 20, 2010 at 11:31 AM, Colin 't Hart <colinth...@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 any data 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 data exceptions. > > 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
