Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

An Awesome One-Liner for PostgreSQL

DZone's Guide to

An Awesome One-Liner for PostgreSQL

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

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?


It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:

Published at DZone with permission of Chris Travers, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}