Send Netdot-users mailing list submissions to
[email protected]
To subscribe or unsubscribe via the World Wide Web, visit
https://osl.uoregon.edu/mailman/listinfo/netdot-users
or, via email, send a message with subject or body 'help' to
[email protected]
You can reach the person managing the list at
[email protected]
When replying, please edit your Subject line so it is more specific
than "Re: Contents of Netdot-users digest..."
Today's Topics:
1. ipblock view with human readable addresses (Matt Zagrabelny)
2. Re: ipblock view with human readable addresses (Robin Johansson)
----------------------------------------------------------------------
Message: 1
Date: Fri, 3 Feb 2017 10:18:08 -0600
From: Matt Zagrabelny <[email protected]>
Subject: [Netdot-users] ipblock view with human readable addresses
To: "[email protected]" <[email protected]>
Message-ID:
<caolfk3xq3c3tbztxveziwzjh3pdimnej0dutepottxzv4e+...@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Greetings,
I'm sharing a PostgreSQL view that I just created to have
human-readable addresses in addition to the rest of the data in the
ipblock table. I needed to create a custom function to convert the
NUMERIC address to the INET datatype. I've tested it on Pg 9.6.1 and
9.4.9, but I'd imagine it would run on any mildly recent version.
Perhaps this will be useful to others, too:
DROP FUNCTION IF EXISTS ip_as_int_to_inet(NUMERIC, INTEGER);
CREATE FUNCTION ip_as_int_to_inet(ip_as_int NUMERIC, version INTEGER)
RETURNS INET AS $$
DECLARE
ip_string TEXT := '';
quotient NUMERIC;
BEGIN
IF version = 4 THEN
FOR i IN REVERSE 3..0 LOOP
quotient := div(ip_as_int, (256 ^ i::NUMERIC));
ip_as_int := mod(ip_as_int, (256 ^ i::NUMERIC));
ip_string := ip_string || quotient::TEXT;
IF i > 0 THEN
ip_string := ip_string || '.';
END IF;
END LOOP;
ELSIF version = 6 THEN
FOR i IN REVERSE 31..0 LOOP
quotient := div(ip_as_int, (16 ^ i::NUMERIC));
ip_as_int := mod(ip_as_int, (16 ^ i::NUMERIC));
ip_string := ip_string || to_hex(quotient::INTEGER);
IF mod(i, 4) = 0 and i > 0 THEN
ip_string := ip_string || ':';
END IF;
END LOOP;
END IF;
RETURN ip_string::INET;
END
$$ LANGUAGE plpgsql;
DROP VIEW IF EXISTS ipblock_with_inets;
CREATE VIEW ipblock_with_inets AS
SELECT
ip_as_int_to_inet(address, version) AS inet,
*
FROM
ipblock
;
-m
------------------------------
Message: 2
Date: Fri, 03 Feb 2017 17:58:01 +0100
From: Robin Johansson <[email protected]>
Subject: Re: [Netdot-users] ipblock view with human readable addresses
To: "[email protected]" <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset="UTF-8"
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512
Hi,
In the long run it's probably better to migrate away from special
formats in the database and incorporate the already existing cidr type
that handles both ipv4 and ipv6 address/prefixes and presents them
human readable directly.
I guess it's the way it is due to mysql lacking that kind if datatype.
/Robin
On Fri, 2017-02-03 at 10:18 -0600, Matt Zagrabelny wrote:
> Greetings,
>
> I'm sharing a PostgreSQL view that I just created to have
> human-readable addresses in addition to the rest of the data in the
> ipblock table. I needed to create a custom function to convert the
> NUMERIC address to the INET datatype. I've tested it on Pg 9.6.1 and
> 9.4.9, but I'd imagine it would run on any mildly recent version.
>
> Perhaps this will be useful to others, too:
>
> DROP FUNCTION IF EXISTS ip_as_int_to_inet(NUMERIC, INTEGER);
> CREATE FUNCTION ip_as_int_to_inet(ip_as_int NUMERIC, version INTEGER)
> RETURNS INET AS $$
> DECLARE
> ????ip_string TEXT := '';
> ????quotient NUMERIC;
> BEGIN
> ????IF version = 4 THEN
> ????????FOR i IN REVERSE 3..0 LOOP
> ????????????quotient := div(ip_as_int, (256 ^ i::NUMERIC));
> ????????????ip_as_int := mod(ip_as_int, (256 ^ i::NUMERIC));
> ????????????ip_string := ip_string || quotient::TEXT;
> ????????????IF i > 0 THEN
> ????????????????ip_string := ip_string || '.';
> ????????????END IF;
> ????????END LOOP;
> ????ELSIF version = 6 THEN
> ????????FOR i IN REVERSE 31..0 LOOP
> ????????????quotient := div(ip_as_int, (16 ^ i::NUMERIC));
> ????????????ip_as_int := mod(ip_as_int, (16 ^ i::NUMERIC));
> ????????????ip_string := ip_string || to_hex(quotient::INTEGER);
> ????????????IF mod(i, 4) = 0 and i > 0 THEN
> ????????????????ip_string := ip_string || ':';
> ????????????END IF;
> ????????END LOOP;
> ????END IF;
> ????RETURN ip_string::INET;
> END
> $$ LANGUAGE plpgsql;
>
> DROP VIEW IF EXISTS ipblock_with_inets;
> CREATE VIEW ipblock_with_inets AS
> ????SELECT
> ????????ip_as_int_to_inet(address, version) AS inet,
> ????????*
> ????FROM
> ????????ipblock
> ;
>
> -m
> _______________________________________________
> Netdot-users mailing list
> [email protected]
> https://osl.uoregon.edu/mailman/listinfo/netdot-users
-----BEGIN PGP SIGNATURE-----
iQIzBAEBCgAdFiEEFK1gZxyJHk+/z1R1ZYrmSsR5nLIFAliUtpkACgkQZYrmSsR5
nLJoeg//YVwBfg14Ix/sJedfYbtsFnbeJWn40BsLrvIZ2smmppmkTJZ+pls1vUvg
O/Fo+vwe6c2jygo6aIwKWKEUDdpwH6snS6P3sljpD1AqazQud6Pn7oYPpzDyt7al
/MVHHMQxtSwYIyp0zp9MpSVtC6RR3KAeoIt8NMSu4KLRrR/PtQH4Jfgv58f4n9g6
BzltKFM/66d1lltyzLLtlx6IcyCG/ZQus3kb1pw2uI6yoFvBLfwoNTwoy1qYbFAT
/ck7w+WsN1FivRr5FLjPbwWo8DfpTWFG5/FeiF+dyHDl1zS8jO5SxAvGvNgYZOJF
A+XEe5Mejz9d4T72obISVouzpNpplB2kgY6Um2+ezSSoX6NmxFAKh9etOCmnz2gU
TpqTldZkULtf2dAFjRSuBKNug4vzLa1cjBHvOUO1MVFTqlhQs3/NpRYUHEN5u8cv
/uljwCq1Ww+y4913Nl++gSjA8k1tohDHUyoprRKOTcZGwR3CVkbyUb7lPpIAv2jI
+HiM6057sUynLS4Cg7zkL+FpjWDj4gEVvGOHKxgcJczjE4lccY2MCSvM3IhTlfQs
GSbLMyrOW0qUUTQhhX/knYDxIu2RW1gN7/vWkJjBmIpg1s3wWYg+YWIyntWqQH6k
ARKRV8O/ECjkxXDm8lSs3QQ114GBi6YEByhmuRiTKKXPg3BQhTM=
=+HGW
-----END PGP SIGNATURE-----
------------------------------
_______________________________________________
Netdot-users mailing list
[email protected]
https://osl.uoregon.edu/mailman/listinfo/netdot-users
End of Netdot-users Digest, Vol 94, Issue 2
*******************************************
