Thread: VARCHAR to CIDR type cast

VARCHAR to CIDR type cast

From

Nicolas Huillard

Date:

06 June 2001, 09:55:32

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

RE: VARCHAR to CIDR type cast

From

Nicolas Huillard

Date:

08 June 2001, 09:18:57

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

Re: RE: VARCHAR to CIDR type cast

From

Alex Pilosov

Date:

08 June 2001, 16:49:17

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

RE: RE: VARCHAR to CIDR type cast

From

Nicolas Huillard

Date:

11 June 2001, 16:21:02

> -----Message d'origine-----
> De:    Alex Pilosov [SMTP:alex@pilosoft.com]
> Date:    vendredi 8 juin 2001 19:59
> À:    Nicolas Huillard
> Cc:    'pgsql-general@postgresql.org'
> Objet:    Re: [GENERAL] RE: VARCHAR to CIDR type cast
>
...
>
> 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....

That's what I'm planning...
But I have not such function code to start with. Is there somewhere
something like that :
* a simple C function with all the PostgreSQL stuff
* that simply call a PG's internal function (that will be cidr_in and
inet_in, for me)

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

OK : the external cast function will only convert the
"length-info-at-the-beginning" string into a "null-terminated" string, then
call cidr_in...
Really simple, in fact, but with a lot of stuff all around, isn't it ?

NH

RE: RE: VARCHAR to CIDR type cast

From

Alex Pilosov

Date:

11 June 2001, 17:12:46

I'm writing these functions right now...Tonight i'll submit this to this
group :)

On Mon, 11 Jun 2001, Nicolas Huillard wrote:

> That's what I'm planning...
> But I have not such function code to start with. Is there somewhere
> something like that :
> * a simple C function with all the PostgreSQL stuff
> * that simply call a PG's internal function (that will be cidr_in and
> inet_in, for me)
>
> >
> > 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?
>
> OK : the external cast function will only convert the
> "length-info-at-the-beginning" string into a "null-terminated" string, then
> call cidr_in...
> Really simple, in fact, but with a lot of stuff all around, isn't it ?
>
> NH
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>