Thread: Re: int to inet conversion [or Re: inet to bigint?]

Re: int to inet conversion [or Re: inet to bigint?]

From
Kai
Date:
Hello All,

I've been pondering the discussed subject a few times, and came along a few
things that I think are missing from the default set of typeconversions
within postgres.

After working regularly with inet values in sql, it would be nice to be able
to do this:
=> select '192.168.1.1'::inet + 1 as result;   result    ------------- 192.168.1.2(1 row)
=> select '192.168.1.255'::inet - '192.168.1.0'::inet as difference;   difference---------------- 255(1 row)

or simply this:
=> select '192.168.1.1'::inet::bigint bigint ------------ 3232235777


In the old postgres 7.3 the data was stored in the database being a big
integer anyway, but in the new ipv6 compatible stuff I lost track. I can
probably write the functions in C if theres more interest in them, but I'm
not on track on how to define all the casting stuff in the postgresql system
tables, nor the sticky subject on how to handle ipv6.


Or maybe someone else was pondering the idea too and is far better at
writing C? :-)


My conclusion is that the selects above should be among the default set of
operations on inet values in PostgreSQL, being subtraction and addition. If
not I'd like to be proven wrong.


Regards,


Kai


Re: int to inet conversion [or Re: inet to bigint?]

From
Andrew Dunstan
Date:
How do you intend to handle the mask and family parts of the object in 
converting it to an int, not to mention the ipv6 difficulties you mention?

A better way might be to add some extra functions, ISTM.

cheers

andrew

Kai wrote:

>Hello All,
>
>I've been pondering the discussed subject a few times, and came along a few
>things that I think are missing from the default set of typeconversions
>within postgres.
>
>After working regularly with inet values in sql, it would be nice to be able
>to do this:
>
>    => select '192.168.1.1'::inet + 1 as result;
>       result    
>    -------------
>     192.168.1.2
>    (1 row)
>
>    => select '192.168.1.255'::inet - '192.168.1.0'::inet as difference;
>       difference
>    ----------------
>     255
>    (1 row)
>
>or simply this:
>
>    => select '192.168.1.1'::inet::bigint
>     bigint 
>    ------------
>     3232235777
>
>
>In the old postgres 7.3 the data was stored in the database being a big
>integer anyway, but in the new ipv6 compatible stuff I lost track. I can
>probably write the functions in C if theres more interest in them, but I'm
>not on track on how to define all the casting stuff in the postgresql system
>tables, nor the sticky subject on how to handle ipv6.
>
>
>Or maybe someone else was pondering the idea too and is far better at
>writing C? :-)
>
>
>My conclusion is that the selects above should be among the default set of
>operations on inet values in PostgreSQL, being subtraction and addition. If
>not I'd like to be proven wrong.
>
>
>Regards,
>
>
>Kai
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>  
>


Re: int to inet conversion [or Re: inet to bigint?]

From
Christopher Kings-Lynne
Date:
Hi Kai,

There are some rather simplistic functions to convert 32bit inet values 
to and from bigints in the mysql compatibility project:

http://pgfoundry.org/projects/mysqlcompat/

In the miscellaneous.sql.

Chris

Kai wrote:
> Hello All,
> 
> I've been pondering the discussed subject a few times, and came along a few
> things that I think are missing from the default set of typeconversions
> within postgres.
> 
> After working regularly with inet values in sql, it would be nice to be able
> to do this:
> 
>     => select '192.168.1.1'::inet + 1 as result;
>        result    
>     -------------
>      192.168.1.2
>     (1 row)
> 
>     => select '192.168.1.255'::inet - '192.168.1.0'::inet as difference;
>        difference
>     ----------------
>      255
>     (1 row)
> 
> or simply this:
> 
>     => select '192.168.1.1'::inet::bigint
>      bigint 
>     ------------
>      3232235777
> 
> 
> In the old postgres 7.3 the data was stored in the database being a big
> integer anyway, but in the new ipv6 compatible stuff I lost track. I can
> probably write the functions in C if theres more interest in them, but I'm
> not on track on how to define all the casting stuff in the postgresql system
> tables, nor the sticky subject on how to handle ipv6.
> 
> 
> Or maybe someone else was pondering the idea too and is far better at
> writing C? :-)
> 
> 
> My conclusion is that the selects above should be among the default set of
> operations on inet values in PostgreSQL, being subtraction and addition. If
> not I'd like to be proven wrong.
> 
> 
> Regards,
> 
> 
> Kai
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq


Re: int to inet conversion [or Re: inet to bigint?]

From
Volkan YAZICI
Date:
On Dec 08 04:36, Kai wrote:
> After working regularly with inet values in sql, it would be nice to be able
> to do this:
> 
>     => select '192.168.1.1'::inet + 1 as result;
>        result    
>     -------------
>      192.168.1.2
>     (1 row)

You may take a look at ip4r[1] project too. For a full list for its
availabilities (like +/- operators) here[2] is the related SQL file.

[1] http://pgfoundry.org/projects/ip4r/
[2] http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/ip4r/ip4r/ip4r.sql.in?rev=1.4&content-type=text/plain


Regards.

-- 
"We are the middle children of history, raised by television to believe
that someday we'll be millionaires and movie stars and rock stars, but
we won't. And we're just learning this fact," Tyler said. "So don't
fuck with us."


Re: int to inet conversion [or Re: inet to bigint?]

From
Bruce Momjian
Date:
We have a patch for this for application to 8.2.

---------------------------------------------------------------------------

Volkan YAZICI wrote:
> On Dec 08 04:36, Kai wrote:
> > After working regularly with inet values in sql, it would be nice to be able
> > to do this:
> > 
> >     => select '192.168.1.1'::inet + 1 as result;
> >        result    
> >     -------------
> >      192.168.1.2
> >     (1 row)
> 
> You may take a look at ip4r[1] project too. For a full list for its
> availabilities (like +/- operators) here[2] is the related SQL file.
> 
> [1] http://pgfoundry.org/projects/ip4r/
> [2] http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/ip4r/ip4r/ip4r.sql.in?rev=1.4&content-type=text/plain
> 
> 
> Regards.
> 
> -- 
> "We are the middle children of history, raised by television to believe
> that someday we'll be millionaires and movie stars and rock stars, but
> we won't. And we're just learning this fact," Tyler said. "So don't
> fuck with us."
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073