Re: inet increment with int

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. +