Postgres Pro
Downloads
Home   >   mailing lists

Thread: dotted quad netmask conversion

dotted quad netmask conversion

From
Steve Clark
Date:
Hello All,

I am working with a postgresql database that has two columns.
One for an ip address and another for the netmask. Both of these
columns are char varying(30). I would like to convert to just
one column as inet. Any one know a slick way to convert the
dotted quad netmask, E.G. 255.255.128.0, to number of bits. I didn't
see any way directly looking at the network functions in the PG documentation.

Thanks in advance for your consideration.
--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
http://www.netwolves.com

Re: dotted quad netmask conversion

From
Jon Nelson
Date:
On Tue, Dec 7, 2010 at 9:17 AM, Steve Clark <sclark@netwolves.com> wrote:
> Hello All,
>
> I am working with a postgresql database that has two columns.
> One for an ip address and another for the netmask. Both of these
> columns are char varying(30). I would like to convert to just
> one column as inet. Any one know a slick way to convert the
> dotted quad netmask, E.G. 255.255.128.0, to number of bits. I didn't
> see any way directly looking at the network functions in the PG
> documentation.


select '1.2.3.4'::inet & '255.255.128.0'::inet;
or
select CAST('1.2.3.4' AS INET) & CAST('255.255.128.0' AS INET);

Be aware that CIDR representation is not as granular as netmask.

http://www.postgresql.org/docs/8.4/interactive/functions-net.html

--
Jon

Re: dotted quad netmask conversion

From
Steve Clark
Date:
On 12/07/2010 10:30 AM, Jon Nelson wrote: 
On Tue, Dec 7, 2010 at 9:17 AM, Steve Clark <sclark@netwolves.com> wrote: 
Hello All,

I am working with a postgresql database that has two columns.
One for an ip address and another for the netmask. Both of these
columns are char varying(30). I would like to convert to just
one column as inet. Any one know a slick way to convert the
dotted quad netmask, E.G. 255.255.128.0, to number of bits. I didn't
see any way directly looking at the network functions in the PG
documentation.   

select '1.2.3.4'::inet & '255.255.128.0'::inet;
or
select CAST('1.2.3.4' AS INET) & CAST('255.255.128.0' AS INET);

Be aware that CIDR representation is not as granular as netmask.

http://www.postgresql.org/docs/8.4/interactive/functions-net.html
Thanks for the response Jon. I should have stated this PG 8.1.x and '&' doesn't exist
for network functions.

select CAST('1.2.3.4' AS INET) & CAST('255.255.128.0' AS INET);
ERROR:  operator does not exist: inet & inet
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.



--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
http://www.netwolves.com

Re: dotted quad netmask conversion

From
Tom Lane
Date:
Steve Clark <sclark@netwolves.com> writes:
> Thanks for the response Jon. I should have stated this PG 8.1.x and '&'
> doesn't exist for network functions.

I don't think & does what you want anyway.  It just does a bit AND on
the two addresses, it doesn't change the masklen property.

There's probably only a small number of distinct netmasks you actually
need to handle in this conversion.  What I'd suggest is writing a simple
function with a CASE statement to translate netmask to an integer mask
length, and then you can use set_masklen to merge that result into the
address value.

            regards, tom lane

Re: dotted quad netmask conversion

From
Steve Clark
Date:
On 12/07/2010 11:43 AM, Tom Lane wrote: 
Steve Clark <sclark@netwolves.com> writes: 
Thanks for the response Jon. I should have stated this PG 8.1.x and '&' 
doesn't exist for network functions.   
I don't think & does what you want anyway.  It just does a bit AND on
the two addresses, it doesn't change the masklen property.

There's probably only a small number of distinct netmasks you actually
need to handle in this conversion.  What I'd suggest is writing a simple
function with a CASE statement to translate netmask to an integer mask
length, and then you can use set_masklen to merge that result into the
address value.
		regards, tom lane
Googling on the net I found a couple of functions that with tweaks for 8.1
seem to work.

CREATE OR REPLACE FUNCTION inet_to_longip(v_t INET)
RETURNS BIGINT AS
$inet_to_longip$
DECLARE
    t1 TEXT;
    t2 TEXT;
    t3 TEXT;
    t4 TEXT;
    i BIGINT;

BEGIN
    t1 := SPLIT_PART(HOST(v_t), '.',1);
    t2 := SPLIT_PART(HOST(v_t), '.',2);
    t3 := SPLIT_PART(HOST(v_t), '.',3);
    t4 := SPLIT_PART(HOST(v_t), '.',4);
    i := (t1::BIGINT << 24) + (t2::BIGINT << 16) +
            (t3::BIGINT << 8) + t4::BIGINT;
    RETURN i;
END;
$inet_to_longip$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION netmask_bits(v_i BIGINT)
RETURNS INTEGER AS
$netmask_msb$
DECLARE
    n INTEGER;

BEGIN
    n := (32-log(2, 4294967296 - v_i ))::integer;
    RETURN n;
END;
$netmask_msb$ LANGUAGE plpgsql STRICT IMMUTABLE;

Which seems to do the trick.

select netmask_bits(inet_to_longip('255.255.255.0'));
 netmask_bits
--------------
           24

select netmask_bits(inet_to_longip('255.255.128.0'));
 netmask_bits
--------------
           17


Thanks all.




--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
http://www.netwolves.com