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