Lists: | pgsql-general |
---|
From: | Steve Clark <sclark(at)netwolves(dot)com> |
---|---|
To: | pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | dotted quad netmask conversion |
Date: | 2010-12-07 15:17:16 |
Message-ID: | 4CFE4FFC.9010500@netwolves.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)clark(at)netwolves(dot)com
http://www.netwolves.com
From: | Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> |
---|---|
To: | Steve Clark <sclark(at)netwolves(dot)com> |
Cc: | pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: dotted quad netmask conversion |
Date: | 2010-12-07 15:30:07 |
Message-ID: | AANLkTimcbum+Kccyr4BDFmC61wsES5bu-HN02tCWCdSB@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Tue, Dec 7, 2010 at 9:17 AM, Steve Clark <sclark(at)netwolves(dot)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
From: | Steve Clark <sclark(at)netwolves(dot)com> |
---|---|
To: | Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> |
Cc: | pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: dotted quad netmask conversion |
Date: | 2010-12-07 16:34:25 |
Message-ID: | 4CFE6211.8050101@netwolves.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 12/07/2010 10:30 AM, Jon Nelson wrote:
> On Tue, Dec 7, 2010 at 9:17 AM, Steve Clark<sclark(at)netwolves(dot)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(dot)clark(at)netwolves(dot)com
http://www.netwolves.com
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Steve Clark <sclark(at)netwolves(dot)com> |
Cc: | Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>, pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: dotted quad netmask conversion |
Date: | 2010-12-07 16:43:39 |
Message-ID: | 23588.1291740219@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Steve Clark <sclark(at)netwolves(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>, pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: dotted quad netmask conversion |
Date: | 2010-12-07 17:42:27 |
Message-ID: | 4CFE7203.9010401@netwolves.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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