LS,
I don't know if this is the right mailing list to post my request. But here
it goes. PostgreSQL has greatly support for data types inet and cidr. But so
far I haven't been able to figure out how one would convert a ip/netmask
(what one will find on a network card) pair into a network cidr.
I've written three functions which help me to help me with my problem:
CREATE OR REPLACE FUNCTION get_masklen(inet)
RETURNS integer AS
$BODY$
DECLARE
_netmask ALIAS FOR $1;
BEGIN
IF _netmask IS NULL THEN return NULL;
ELSIF _netmask = '255.255.255.255'::inet THEN return 32;
ELSIF _netmask = '255.255.255.254'::inet THEN return 31;
ELSIF _netmask = '255.255.255.252'::inet THEN return 30;
ELSIF _netmask = '255.255.255.248'::inet THEN return 29;
ELSIF _netmask = '255.255.255.240'::inet THEN return 28;
ELSIF _netmask = '255.255.255.224'::inet THEN return 27;
ELSIF _netmask = '255.255.255.192'::inet THEN return 26;
ELSIF _netmask = '255.255.255.128'::inet THEN return 25;
ELSIF _netmask = '255.255.255.0'::inet THEN return 24;
ELSIF _netmask = '255.255.254.0'::inet THEN return 23;
ELSIF _netmask = '255.255.252.0'::inet THEN return 22;
ELSIF _netmask = '255.255.248.0'::inet THEN return 21;
ELSIF _netmask = '255.255.240.0'::inet THEN return 20;
ELSIF _netmask = '255.255.224.0'::inet THEN return 19;
ELSIF _netmask = '255.255.192.0'::inet THEN return 18;
ELSIF _netmask = '255.255.128.0'::inet THEN return 17;
ELSIF _netmask = '255.255.0.0'::inet THEN return 16;
ELSIF _netmask = '255.254.0.0'::inet THEN return 15;
ELSIF _netmask = '255.252.0.0'::inet THEN return 14;
ELSIF _netmask = '255.248.0.0'::inet THEN return 13;
ELSIF _netmask = '255.240.0.0'::inet THEN return 12;
ELSIF _netmask = '255.224.0.0'::inet THEN return 11;
ELSIF _netmask = '255.192.0.0'::inet THEN return 10;
ELSIF _netmask = '255.128.0.0'::inet THEN return 9;
ELSIF _netmask = '255.0.0.0'::inet THEN return 8;
ELSIF _netmask = '254.0.0.0'::inet THEN return 7;
ELSIF _netmask = '252.0.0.0'::inet THEN return 6;
ELSIF _netmask = '248.0.0.0'::inet THEN return 5;
ELSIF _netmask = '240.0.0.0'::inet THEN return 4;
ELSIF _netmask = '224.0.0.0'::inet THEN return 3;
ELSIF _netmask = '192.0.0.0'::inet THEN return 2;
ELSIF _netmask = '128.0.0.0'::inet THEN return 1;
ELSIF _netmask = '0.0.0.0'::inet THEN return 0;
ELSE
RAISE EXCEPTION 'get_masklen(''%''): Invalid netmask', _netmask;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT
COST 100;
CREATE OR REPLACE FUNCTION set_masklen(inet,inet)
RETURNS inet AS
$BODY$
BEGIN
RETURN set_masklen($1,get_masklen($2))::inet;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT
COST 100;
CREATE OR REPLACE FUNCTION network(inet,inet)
RETURNS cidr AS
$BODY$
BEGIN
RETURN set_masklen($1,get_masklen($2))::cidr;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT
COST 100;
# SELECT network('1.2.3.4'::inet,'255.255.0.0'::inet);
network
-------------
1.2.0.0/16
(1 row)
My question is: are these interesting enough to adopt in the PostgreSQL core
set of functions. Ideally not in plpgsql but written in C. A trivial thing
for one of the core team to convert into C if you ask me. If no one want to
do this but still interest of adding the routines to the core set of
functions I can do the rewrite in C my self.... Well need to dust of my C
skills a bit first to be honest.
Happy hacking,
|N.
