Postgres Pro
Downloads
Home   >   mailing lists

Thread: inet to bigint?

inet to bigint?

From
Christopher Kings-Lynne
Date:
OK, I give up - how do I convert an INET type to a NUMERIC 
representation of its network address?

Is there a quick and easy way?

Chris

Re: inet to bigint?

From
Michael Fuhr
Date:
On Tue, Dec 06, 2005 at 03:31:59PM +0800, Christopher Kings-Lynne wrote:
> OK, I give up - how do I convert an INET type to a NUMERIC 
> representation of its network address?

How about:

CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
use Socket;
return unpack("N", inet_aton($_[0]));
$$ LANGUAGE plperlu IMMUTABLE STRICT;

SELECT inet2num('127.0.0.1'); inet2num  
------------2130706433
(1 row)

-- 
Michael Fuhr

Re: inet to bigint?

From
hubert depesz lubaczewski
Date:
On 12/6/05, Michael Fuhr <mike@fuhr.org> wrote:
How about:
CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
use Socket;
return unpack("N", inet_aton($_[0]));
$$ LANGUAGE plperlu IMMUTABLE STRICT;

you can use this one:
... AS $$
return unpack("N", pack("C4", split(/\./, $_[0])));
$$ language plperl IMMUTABLE STRICT;

to avoid the need to use untrusted languages.
it is less readable thought :(

depesz

Re: inet to bigint?

From
Christopher Kings-Lynne
Date:
PL/SQL or PL/PGSQL...

Chris

Michael Fuhr wrote:
> On Tue, Dec 06, 2005 at 03:31:59PM +0800, Christopher Kings-Lynne wrote:
> 
>>OK, I give up - how do I convert an INET type to a NUMERIC 
>>representation of its network address?
> 
> 
> How about:
> 
> CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
> use Socket;
> return unpack("N", inet_aton($_[0]));
> $$ LANGUAGE plperlu IMMUTABLE STRICT;
> 
> SELECT inet2num('127.0.0.1');
>   inet2num  
> ------------
>  2130706433
> (1 row)
>

Re: inet to bigint?

From
Michael Fuhr
Date:
On Tue, Dec 06, 2005 at 03:51:17PM +0800, Christopher Kings-Lynne wrote:
> PL/SQL or PL/PGSQL...

Sheesh, arbitrary restrictions ;-)  Something like this then:

CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
DECLARE   a  text[] := string_to_array(host($1), '.');
BEGIN   RETURN a[1]::numeric * 16777216 +           a[2]::numeric * 65536 +           a[3]::numeric * 256 +
a[4]::numeric;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

-- 
Michael Fuhr

Re: inet to bigint?

From
Christopher Kings-Lynne
Date:
> Sheesh, arbitrary restrictions ;-)  Something like this then:
> 
> CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
> DECLARE
>     a  text[] := string_to_array(host($1), '.');
> BEGIN
>     RETURN a[1]::numeric * 16777216 + 
>            a[2]::numeric * 65536 + 
>            a[3]::numeric * 256 + 
>            a[4]::numeric;
> END;
> $$ LANGUAGE plpgsql IMMUTABLE STRICT;

Cool, and now the reverse? :D

(I'll credit you in the MySQL Compat Library code btw)  If you're 
interested, you'd be welcome to join the project btw...

Chris

Re: inet to bigint?

From
Michael Fuhr
Date:
On Tue, Dec 06, 2005 at 01:05:12AM -0700, Michael Fuhr wrote:
> CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
> DECLARE
>     a  text[] := string_to_array(host($1), '.');
> BEGIN
>     RETURN a[1]::numeric * 16777216 + 
>            a[2]::numeric * 65536 + 
>            a[3]::numeric * 256 + 
>            a[4]::numeric;
> END;
> $$ LANGUAGE plpgsql IMMUTABLE STRICT;

I should point out that this is only for IPv4, so a family() check
might be in order.

-- 
Michael Fuhr

Re: inet to bigint?

From
Michael Fuhr
Date:
On Tue, Dec 06, 2005 at 04:10:22PM +0800, Christopher Kings-Lynne wrote:
> >Sheesh, arbitrary restrictions ;-)  Something like this then:
> >
> >CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
> >DECLARE
> >    a  text[] := string_to_array(host($1), '.');
> >BEGIN
> >    RETURN a[1]::numeric * 16777216 + 
> >           a[2]::numeric * 65536 + 
> >           a[3]::numeric * 256 + 
> >           a[4]::numeric;
> >END;
> >$$ LANGUAGE plpgsql IMMUTABLE STRICT;
> 
> Cool, and now the reverse? :D

Tom posted one just a couple of days ago:

http://archives.postgresql.org/pgsql-general/2005-12/msg00191.php

> (I'll credit you in the MySQL Compat Library code btw)  If you're 
> interested, you'd be welcome to join the project btw...

I haven't been following it but I might have some time.  Is there
a TODO list?  The one I see on pgfoundry is empty.

-- 
Michael Fuhr