Re: testing castability of VARCHAR data to INET/CIDR

# mike(at)fuhr(dot)org / 2005-07-23 06:04:55 -0600:
> 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

I was (still am) afraid this would be too long in the teeth on
my ~3M rows * N tables, and ITMT wrote one in C; it's basically

PG_RETURN_BOOL(inet_aton(PG_GETARG_TEXT_P(0)));

Not a full "this would make a valid INET / CIDR value" test, but
good enough for my needs.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991