Ilya Kovalenko posted some code at in a thread starting at
http://archives.postgresql.org/pgsql-hackers/2005-04/msg00417.php
which lead to the TODO item:
* Allow INET + INT4 to increment the host part of the address, or
throw an error on overflow
I think that the naively coded function attached does what is needed, e.g.,
CREATE OR REPLACE FUNCTION inet_inc(inet, int4)
RETURNS inet
AS '/tmp/inet.so','inet_inc'
LANGUAGE C STRICT;
CREATE OPERATOR + (
leftarg = inet,
rightarg = int4,
procedure = inet_inc
);
test=# select '192.168.0.1/24'::inet + 300;
ERROR: Increment (300) too big for network (/24)
test=# select '192.168.0.1/24'::inet + 254;
?column?
------------------
192.168.0.255/24
(1 row)
test=# select '192.168.0.1/24'::inet + 255;
ERROR: Increment (255) takes address (192.168.0.1) out of its network (/24)
test=# select '192.168.0.1/24'::inet + -2;
ERROR: Increment (-2) takes address (192.168.0.1) out of its network (/24)
test=# select '255.255.255.254/0'::inet + 2;
ERROR: Increment (2) takes address (255.255.255.254) out of its network (/0)
and just for fun:
create table list (
host inet
);
insert into list values ('192.168.0.1/24');
insert into list values ('192.168.0.2/24');
insert into list values ('192.168.0.4/24');
insert into list values ('192.168.0.5/24');
insert into list values ('192.168.0.6/24');
insert into list values ('192.168.0.8/24');
insert into list values ('192.168.0.9/24');
insert into list values ('192.168.0.10/24');
insert into list values ('192.168.1.1/24');
insert into list values ('192.168.1.3/24');
select host+1 from list
where host+1 <<= '192.168.1.0/24'
and not exists
( select 1
from list
where host=host+1
and host << '192.168.1.0/24' )
limit 1;
If you agree that this is the right thing, I can code it less
naively, (Ilya rightly uses ntohl/htonl), create the operator's
commutator, provide a patch which makes it a built-in, and some
obvious documentation.
Cheers,
Patrick
/* From the TODO:
* Allow INET + INT4 to increment the host part of the address, or
* throw an error on overflow
*/
#include "postgres.h"
#include <sys/socket.h>
#include "fmgr.h"
#include "utils/inet.h"
PG_FUNCTION_INFO_V1(inet_inc);
Datum
inet_inc(PG_FUNCTION_ARGS)
{
inet *in = PG_GETARG_INET_P(0), *out;
int32 inc = PG_GETARG_INT32(1);
inet_struct *src, *dst;
uint32 netmask, host, newhost;
int i;
src = (inet_struct *)VARDATA(in);
if (src->family != PGSQL_AF_INET)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("Function \"inet_inc\" only supports AF_INET "
"addresses")));
/* avoid int32 overflow when bits == 0 */
netmask = (src->bits == 0) ? 0 : (~((1 << (32 - src->bits)) - 1));
/* if (inc doesn't fit in src->bits) overflow */
if ((abs(inc) & ~netmask) != abs(inc))
ereport(ERROR,
(errcode(ERRCODE_DATA_EXCEPTION),
errmsg("Increment (%d) too big for network (/%d)",
inc,
src->bits)));
/* can do this with htonl/ntohl */
host = 0;
for (i=0; i<4; ++i)
host |= src->ipaddr[i] << (8 * (3-i));
newhost = host + inc;
if (((host & netmask) != (newhost & netmask))
|| (inc>0 && newhost<host)
|| (inc<0 && newhost>host))
ereport(ERROR,
(errcode(ERRCODE_DATA_EXCEPTION),
errmsg("Increment (%d) takes address (%d.%d.%d.%d) out
of its "
"network (/%d)", inc,
src->ipaddr[0], src->ipaddr[1], src->ipaddr[2],
src->ipaddr[3], src->bits)));
out = (inet *)palloc0(VARHDRSZ + sizeof(inet_struct));
dst = (inet_struct *)VARDATA(out);
dst->family = src->family;
dst->bits = src->bits;
dst->type = src->type;
for (i=0; i<4; ++i)
dst->ipaddr[i] = (newhost >> (8 * (3-i))) & 0xff;
for (i=4; i<16; ++i)
dst->ipaddr[i] = 0;
VARATT_SIZEP(out) = VARHDRSZ + sizeof(dst->family) + sizeof(dst->bits)
+ sizeof(dst->type) + 4;
PG_RETURN_INET_P(out);
}
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
