Re: [GENERAL] dotted quad netmask conversion
On 12/07/2010 11:43 AM, Tom Lane wrote: Steve Clark 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.cl...@netwolves.com http://www.netwolves.com
Re: [GENERAL] dotted quad netmask conversion
Steve Clark 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dotted quad netmask conversion
On 12/07/2010 10:30 AM, Jon Nelson wrote: On Tue, Dec 7, 2010 at 9:17 AM, Steve Clark 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.cl...@netwolves.com http://www.netwolves.com
Re: [GENERAL] dotted quad netmask conversion
On Tue, Dec 7, 2010 at 9:17 AM, Steve Clark 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general