Re: testing castability of VARCHAR data to INET/CIDR

On Sat, Jul 23, 2005 at 11:31:23AM +0200, Roman Neuhauser wrote:
>
> I have a VARCHAR column containing mostly ip addresses, with an
> occasional piece of junk, and would like to transfer this data to an
> INET column. The UPDATE (SET inet_col = CAST(vc_col AS INET)) aborts
> as soon as it hits an invalid datum. I'm looking for a way to add
> something like WHERE IS_CASTABLE(vc_col, INET) to the update.

If you're using 8.0, then you could write a PL/pgSQL function that
attempts to make the cast and traps INVALID_TEXT_REPRESENTATION.

http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/