VARCHAR to CIDR type cast

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

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:

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

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

On Mon, 11 Jun 2001, Nicolas Huillard wrote: