FYI, 8.2 will have this and more based on this applied patch: Add INET/CIDR operators: and, or, not, plus int8, minus int8, and inet minus inet. Stephen R. van den Berg
--------------------------------------------------------------------------- Patrick Welche wrote: > 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 [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match