Re: dotted quad netmask conversion

On 12/07/2010 11:43 AM, Tom Lane wrote:
> Steve Clark<sclark(at)netwolves(dot)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(dot)clark(at)netwolves(dot)com
http://www.netwolves.com