Re: RE: VARCHAR to CIDR type cast - Mailing list pgsql-general

If you have a string inside the query, you must first cast it to inet then
cast inet further to cidr.

users=> select '128.8.3.2/20'::inet::cidr;
?column?
------------
128.8.3.2/20


However, you say that you have a varchar in database already. As strange
as it may be, there isn't a cast function from any char type to any
network type. I'm sure its an oversight, its not hard to write such a
function based on inet_in....

Unfortunately, you cannot use inet_in function to cast a varchar, since it
expects as input slightly different data (a null-terminated string),
while varchar is different (it has length info in beginning). I remember
there was a discussion about creation of a 'cstring' datatype to support
such conversions (ie convert xxx to yyy via xxx_out and yyy_in), but I
don't see any result...Anyone?

On Fri, 8 Jun 2001, Nicolas Huillard wrote:

> So, nobody (even hackers) know how to work around this ?
> Or this problem is solved in 7.x, and nobody wants to bother with 6.5.3 ?
> Or the question was so complicated that nobody understood it ?
>
> Sorry to insist, but I really don't want to modify my DB structure (there
> are implications far away from Postgres, because the DB is replicated with
> many Access DB's, etc)
>
> NH
>
> > -----Message d'origine-----
> > De:    Nicolas Huillard [SMTP:nhuillard@ghs.fr]
> > Date:    mercredi 6 juin 2001 12:55
> > �:    'pgsql-general@postgresql.org'
> > Objet:    VARCHAR to CIDR type cast
> >
> > Hello,
> >
> > I use Postgres 6.5.3.
> > The following query doesn't work :
> >
> > nhuillard=> SELECT login FROM vue_auth WHERE (login ~ '^[0-9./]+$') AND
> (login::cidr >> '192.168.200.109'::inet);
> > ERROR:  No such function 'cidr' with the specified attributes
> >
> > This query look for login (varchar) that look like IP network adresses
> (cidr), then check if the provided IP adresses is within this network.
> >
> > The only cidr function that exist in the catalog is "cidr_in" :
> >
> > nhuillard=> SELECT t.typname as result, p.proname as function,
> substr(oid8types(p.proargtypes),1,14) as arguments,
> substr(obj_description(p.oid),1,34) as description FROM pg_proc p, pg_type
> t WHERE (p.prorettype = t.oid) and (t.typname = 'cidr') ORDER BY result,
> function, arguments;
> > result|function|arguments|description
> > ------+--------+---------+-----------
> > cidr  |cidr_in |         |(internal)
> > (1 row)
> >
> > This function seems to be the input function, but can't be used for
> casting... Is there a way to use it for casting varchar to cidr, or
> something else ?
> > I tried the following :
> >
> > nhuillard=> create function cidr(text) returns cidr as 'cidr_in' language
> 'internal';
> > CREATE
> > nhuillard=> SELECT login FROM vue_auth WHERE (login ~ '^[0-9./]+$') AND
> (login::cidr >> '192.168.200.109'::inet);
> > ERROR:  could not parse "s"
> >
> > Any advice ?
> >
> > Nicolas Huillard
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>