How to generate a list of IP addresses from a given CIDR in PostgreSQL
If we need to manage a list of IP addresses in database, there are some ways to generate a list of IP addresses.For example, we can generate a list of IP addresses in Microsoft EXCEL, but we need to generate a list of IP address in EXCEL, then create SQL INSERT statements. Also, if we need to generate a list of IP addresses based on user's input, we cannot do it in EXCEL. So we need to use other ways.
In order to generate a list of IP addresses based on a given CIDR (E.g. 192.168.1.0/24) in PostgreSQL, we can use the following PL/PgSQL function. If you have a web interface, you can call the database function on your web application.
CREATE OR REPLACE FUNCTION generate_ips_from_cidr(cidr)
RETURNS SETOF inet AS
$BODY$
SELECT ip FROM (
SELECT $1 + s AS ip
FROM generate_series(1, broadcast($1) - (network($1)) - 1) s
) ips
WHERE host(ips.ip) NOT LIKE '%.0' AND host(ips.ip) NOT LIKE '%.255';
$BODY$
LANGUAGE sql IMMUTABLE STRICT;
The above database function doesn't return ".0" and ".255" IP addresses since those are network address and broadcast address, but if you need them, please get rid of "WHERE host(ips.ip) NOT LIKE '%.0' AND host(ips.ip) NOT LIKE '%.255'" condition from the function.
Once you created the above PL/PgSQL function in PostgreSQL database, you can call it as follows.
SELECT host(generate_ips_from_cidr)
FROM generate_ips_from_cidr('192.168.1.0/24');
The reason why I use host() function in the SELECT statement is that the function returns like "192.168.1.1/24", but I only need "192.168.1.1" so that I need to call the host() function. The function returns the following results.
"192.168.1.1" "192.168.1.2" "192.168.1.3" "192.168.1.4" "192.168.1.5" .......... "192.168.1.250" "192.168.1.251" "192.168.1.252" "192.168.1.253" "192.168.1.254"
I made sure that the database function works perfect in PostgreSQL-8.2 or above versions.
