Обсуждение: testing castability of VARCHAR data to INET/CIDR

Hello,

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.
Is there anything ready to use? I don't see anything directly usable
in src/backend/utils/adt/network.c.

--
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

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/

# mike@fuhr.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