NoSQL Zone is brought to you in partnership with:

Chris Travers is the most active developer of LedgerSMB and have been working with PostgreSQL since 1999. For the last five years he has spent extensive time with the more advanced features of this database management system. At the same time, PostgreSQL has continued to grow in these areas. Chris is a DZone MVB and is not an employee of DZone and has posted 30 posts at DZone. You can read more from them at their website. View Full User Profile

An Awesome One-Liner for PostgreSQL

01.13.2013
| 1437 views |
  • submit to reddit

Recently I was looking at options for exploring CIDR blocks in PostgreSQL.  In particular, I was wondering about checking a CIDR block for unallocated IP addresses in another table.

I had been aware of network address types in PostgreSQL for some time but had not been aware of how powerful they actually were.  I decided to write a function to expand a CIDR bock into a list of IP blocks.  While my initial version wasn't perfect (it includes network and broadcast addresses in the block), changing that will not be hard.

The first version was:

CREATE OR REPLACE FUNCTION all_ips(cidr)
RETURNS SETOF inet LANGUAGE SQL IMMUTABLE AS
$$
 select $1 + s from generate_series(0,  broadcast($1) - network($1)) s;
$$;

That's it.

To exclude network and broadcast addresses, two simple modifications are required:

CREATE OR REPLACE FUNCTION all_ips(cidr)
RETURNS SETOF inet LANGUAGE SQL IMMUTABLE AS
$$
 select $1 + s from generate_series(1,  broadcast($1) - (network($1)) - 1) s;
$$;

And there you have it.  It is fast, or at least as fast as can be expected.

mq_test=# explain analyze
mq_test-# select all_ips('192.168.1.0/24');
                                      QUERY PLAN                               
     
--------------------------------------------------------------------------------
------
 Result  (cost=0.00..0.26 rows=1 width=0) (actual time=0.213..0.511 rows=254 loo
ps=1)
 Total runtime: 0.580 ms
(2 rows)

Ok, not so much for 10.0.0.0/8.....

                                            QUERY PLAN                         
                 
--------------------------------------------------------------------------------
------------------
 Result  (cost=0.00..0.26 rows=1 width=0) (actual time=5977.386..32370.877 rows=
16777214 loops=1)
 Total runtime: 37185.476 ms
(2 rows)

But what do you expect for generating almost 17 million rows?


Published at DZone with permission of Chris Travers, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)