Re: contrib/xinetops for 8.1 "patch"

I've been meaning to send it in for a while now, IMHO it could be
made part of the main distribution as well, but contrib is fine
if not everyone likes it.

I included the source in patch-format, since I didn't feel comfortable
attaching a tar.gz file on this list.
Below is an excerpt from the included README:
-------------------------------- cut here ---------------------------------
inet extended operations
~~~~~~~~~~~~~~~~~~~~~~~~

This directory contains definitions for extended operators on the
inet data type.

Operators available are:

~ & | + -

It supports the "natural" arithmetic with IP addresses and integers.
It is useful for applications which have to hand out and administer
ranges of IP-addresses (like a Radius or DHCP server).

Copyright (c) 2003-2006, Stephen R. van den Berg, The Netherlands.
<srb(at)cuci(dot)nl>

This module is distributed under the same BSD license as PostgreSQL.
-------------------------------- cut here ---------------------------------

diff -ur xinetops.old/Makefile xinetops/Makefile
--- xinetops.old/Makefile 2005-11-13 12:36:39.000000000 +0100
+++ xinetops/Makefile 2005-11-13 12:45:04.000000000 +0100
@@ -0,0 +1,11 @@
+# $Id: Makefile 523 2005-11-13 11:29:44Z srb $
+
+subdir = contrib/xinetops
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+
+MODULES = xinetops
+DATA_built = xinetops.sql
+DOCS = README.xinetops
+
+include $(top_srcdir)/contrib/contrib-global.mk
diff -ur xinetops.old/README.xinetops xinetops/README.xinetops
--- xinetops.old/README.xinetops 2005-11-13 12:36:39.000000000 +0100
+++ xinetops/README.xinetops 2005-11-13 12:45:04.000000000 +0100
@@ -0,0 +1,19 @@
+
+inet extended operations
+~~~~~~~~~~~~~~~~~~~~~~~~
+
+This directory contains definitions for extended operators on the
+inet data type.
+
+Operators available are:
+
+ ~ & | + -
+
+It supports the "natural" arithmetic with IP addresses and integers.
+It is useful for applications which have to hand out and administer
+ranges of IP-addresses (like a Radius or DHCP server).
+
+Copyright (c) 2003-2006, Stephen R. van den Berg, The Netherlands.
+ <srb(at)cuci(dot)nl>
+
+This module is distributed under the same BSD license as PostgreSQL.
diff -ur xinetops.old/xinetops.c xinetops/xinetops.c
--- xinetops.old/xinetops.c 2005-11-13 12:36:39.000000000 +0100
+++ xinetops/xinetops.c 2005-11-13 12:45:04.000000000 +0100
@@ -0,0 +1,254 @@
+/*
+ * PostgreSQL type definitions for extended inet operations.
+ * Copyright (c) 2003-2006, S.R. van den Berg, The Netherlands
+ * <srb(at)cuci(dot)nl>
+ *
+ * This module is distributed under the same BSD license as PostgreSQL.
+ *
+ * $Id: xinetops.c 524 2005-11-13 11:44:43Z srb $
+ */
+
+#include "postgres.h"
+
+#include <errno.h>
+#include <sys/socket.h>
+#include <netinet/in.h>
+#include <arpa/inet.h>
+
+#include "catalog/pg_type.h"
+#include "utils/builtins.h"
+#include "utils/inet.h"
+
+#define ip_family(inetptr) \
+ (((inet_struct *)VARDATA(inetptr))->family)
+
+#define ip_bits(inetptr) \
+ (((inet_struct *)VARDATA(inetptr))->bits)
+
+#define ip_type(inetptr) \
+ (((inet_struct *)VARDATA(inetptr))->type)
+
+#define ip_addr(inetptr) \
+ (((inet_struct *)VARDATA(inetptr))->ipaddr)
+
+static int
+ip_addrsize(inet *inetptr)
+{
+ switch (ip_family(inetptr))
+ {
+ case PGSQL_AF_INET:
+ return 4;
+ case PGSQL_AF_INET6:
+ return 16;
+ default:
+ return -1;
+ }
+}
+
+
+PG_FUNCTION_INFO_V1(inet_not);
+
+Datum
+inet_not(PG_FUNCTION_ARGS)
+{
+ inet *ip = PG_GETARG_INET_P(0);
+ inet *dst;
+
+ dst = (inet *) palloc(VARHDRSZ + sizeof(inet_struct));
+ /* make sure any unused bits are zeroed */
+ MemSet(dst, 0, VARHDRSZ + sizeof(inet_struct));
+
+ {
+ int nb = ip_addrsize(ip);
+ unsigned char*pip = ip_addr(ip);
+ unsigned char*pdst = ip_addr(dst);
+
+ while (nb-->0)
+ {
+ pdst[nb] = pip[nb];
+ }
+ }
+ ip_bits(dst) = ip_bits(ip);
+
+ ip_family(dst) = ip_family(ip);
+ ip_type(dst) = 0;
+ VARATT_SIZEP(dst) = VARHDRSZ
+ + ((char *) ip_addr(dst) - (char *) VARDATA(dst))
+ + ip_addrsize(dst);
+
+ PG_RETURN_INET_P(dst);
+}
+
+PG_FUNCTION_INFO_V1(inet_or);
+
+Datum
+inet_or(PG_FUNCTION_ARGS)
+{
+ inet *ip = PG_GETARG_INET_P(0);
+ inet *ip2 = PG_GETARG_INET_P(1);
+ inet *dst;
+
+ dst = (inet *) palloc(VARHDRSZ + sizeof(inet_struct));
+ /* make sure any unused bits are zeroed */
+ MemSet(dst, 0, VARHDRSZ + sizeof(inet_struct));
+
+ if (ip_family(ip) != ip_family(ip2))
+ elog(ERROR, "mismatch in address family (%d)!=(%d)",
+ ip_family(ip), ip_family(ip2));
+ else
+ {
+ int nb = ip_addrsize(ip);
+ unsigned char*pip = ip_addr(ip);
+ unsigned char*pip2 = ip_addr(ip2);
+ unsigned char*pdst = ip_addr(dst);
+
+ while (nb-->0)
+ {
+ pdst[nb] = pip[nb]|pip2[nb];
+ }
+ }
+ ip_bits(dst) = ip_bits(ip);
+ if (ip_bits(dst)<ip_bits(ip2))
+ ip_bits(dst) = ip_bits(ip2);
+
+ ip_family(dst) = ip_family(ip);
+ ip_type(dst) = 0;
+ VARATT_SIZEP(dst) = VARHDRSZ
+ + ((char *) ip_addr(dst) - (char *) VARDATA(dst))
+ + ip_addrsize(dst);
+
+ PG_RETURN_INET_P(dst);
+}
+
+PG_FUNCTION_INFO_V1(inet_and);
+
+Datum
+inet_and(PG_FUNCTION_ARGS)
+{
+ inet *ip = PG_GETARG_INET_P(0);
+ inet *ip2 = PG_GETARG_INET_P(1);
+ inet *dst;
+
+ dst = (inet *) palloc(VARHDRSZ + sizeof(inet_struct));
+ /* make sure any unused bits are zeroed */
+ MemSet(dst, 0, VARHDRSZ + sizeof(inet_struct));
+
+ if (ip_family(ip) != ip_family(ip2))
+ elog(ERROR, "mismatch in address family (%d)!=(%d)",
+ ip_family(ip), ip_family(ip2));
+ else
+ {
+ int nb = ip_addrsize(ip);
+ unsigned char*pip = ip_addr(ip);
+ unsigned char*pip2 = ip_addr(ip2);
+ unsigned char*pdst = ip_addr(dst);
+
+ while (nb-->0)
+ {
+ pdst[nb] = pip[nb]&pip2[nb];
+ }
+ }
+ ip_bits(dst) = ip_bits(ip);
+ if (ip_bits(dst)<ip_bits(ip2))
+ ip_bits(dst) = ip_bits(ip2);
+
+ ip_family(dst) = ip_family(ip);
+ ip_type(dst) = 0;
+ VARATT_SIZEP(dst) = VARHDRSZ
+ + ((char *) ip_addr(dst) - (char *) VARDATA(dst))
+ + ip_addrsize(dst);
+
+ PG_RETURN_INET_P(dst);
+}
+
+PG_FUNCTION_INFO_V1(inet_min);
+
+Datum
+inet_min(PG_FUNCTION_ARGS)
+{
+ inet *ip = PG_GETARG_INET_P(0);
+ inet *ip2 = PG_GETARG_INET_P(1);
+ int res = 0;
+
+ if (ip_family(ip) != ip_family(ip2))
+ elog(ERROR, "mismatch in address family (%d)!=(%d)",
+ ip_family(ip), ip_family(ip2));
+ else
+ {
+ int nb = ip_addrsize(ip);
+ unsigned char*pip = ip_addr(ip);
+ unsigned char*pip2 = ip_addr(ip2);
+ unsigned sh = 1;
+
+ while (nb-->0)
+ {
+ res += sh*(pip[nb]-pip2[nb]);
+ sh <<= 8;
+ }
+ }
+
+ PG_RETURN_INT32(res);
+}
+
+static Datum
+i_inet_plusi(inet *ip, int iarg)
+{
+ inet *dst;
+
+ dst = (inet *) palloc(VARHDRSZ + sizeof(inet_struct));
+ /* make sure any unused bits are zeroed */
+ MemSet(dst, 0, VARHDRSZ + sizeof(inet_struct));
+
+ {
+ int nb = ip_addrsize(ip);
+ unsigned char*pip = ip_addr(ip);
+ unsigned char*pdst = ip_addr(dst);
+ int carry = 0;
+
+ while (nb-->0)
+ {
+ pdst[nb] = carry = pip[nb]+iarg+carry;
+ iarg >>=8;
+ carry >>=8;
+ }
+ }
+ ip_bits(dst) = ip_bits(ip);
+
+ ip_family(dst) = ip_family(ip);
+ ip_type(dst) = 0;
+ VARATT_SIZEP(dst) = VARHDRSZ
+ + ((char *) ip_addr(dst) - (char *) VARDATA(dst))
+ + ip_addrsize(dst);
+
+ PG_RETURN_INET_P(dst);
+}
+
+PG_FUNCTION_INFO_V1(inet_plusi);
+
+Datum
+inet_plusi(PG_FUNCTION_ARGS)
+{
+ inet *ip = PG_GETARG_INET_P(0);
+ int iarg = PG_GETARG_INT32(1);
+ return i_inet_plusi(ip,iarg);
+}
+
+PG_FUNCTION_INFO_V1(inet_iplus);
+
+Datum
+inet_iplus(PG_FUNCTION_ARGS)
+{
+ int iarg = PG_GETARG_INT32(0);
+ inet *ip = PG_GETARG_INET_P(1);
+ return i_inet_plusi(ip,iarg);
+}
+
+PG_FUNCTION_INFO_V1(inet_mini);
+
+Datum
+inet_mini(PG_FUNCTION_ARGS)
+{
+ inet *ip = PG_GETARG_INET_P(0);
+ int iarg = PG_GETARG_INT32(1);
+ return i_inet_plusi(ip,-iarg);
+}
diff -ur xinetops.old/xinetops.sql.in xinetops/xinetops.sql.in
--- xinetops.old/xinetops.sql.in 2005-11-13 12:36:39.000000000 +0100
+++ xinetops/xinetops.sql.in 2005-11-13 12:45:04.000000000 +0100
@@ -0,0 +1,104 @@
+--
+-- PostgreSQL type definitions for extended inet operations.
+-- Copyright (c) 2003-2006, S.R. van den Berg, The Netherlands
+-- <srb(at)cuci(dot)nl>
+--
+-- This module is distributed under the same BSD license as PostgreSQL.
+--
+-- $Id: xinetops.sql.in 524 2005-11-13 11:44:43Z srb $
+--
+
+-- Adjust this setting to control where the objects get created.
+SET search_path = public;
+
+SET autocommit TO 'on';
+
+--
+-- The various functions doing the work
+--
+
+CREATE FUNCTION inet_not(inet)
+RETURNS inet
+AS 'MODULE_PATHNAME'
+LANGUAGE 'C';
+
+CREATE FUNCTION inet_or(inet, inet)
+RETURNS inet
+AS 'MODULE_PATHNAME'
+LANGUAGE 'C';
+
+CREATE FUNCTION inet_and(inet, inet)
+RETURNS inet
+AS 'MODULE_PATHNAME'
+LANGUAGE 'C';
+
+CREATE FUNCTION inet_plusi(inet, int)
+RETURNS inet
+AS 'MODULE_PATHNAME'
+LANGUAGE 'C';
+
+CREATE FUNCTION inet_iplus(int, inet)
+RETURNS inet
+AS 'MODULE_PATHNAME'
+LANGUAGE 'C';
+
+CREATE FUNCTION inet_mini(inet, int)
+RETURNS inet
+AS 'MODULE_PATHNAME'
+LANGUAGE 'C';
+
+CREATE FUNCTION inet_min(inet, inet)
+RETURNS int
+AS 'MODULE_PATHNAME'
+LANGUAGE 'C';
+
+--
+-- Now the operators.
+--
+
+CREATE OPERATOR ~ (
+ RIGHTARG = inet,
+ PROCEDURE = inet_not
+);
+
+CREATE OPERATOR | (
+ LEFTARG = inet,
+ RIGHTARG = inet,
+ COMMUTATOR = |,
+ PROCEDURE = inet_or
+);
+
+CREATE OPERATOR & (
+ LEFTARG = inet,
+ RIGHTARG = inet,
+ COMMUTATOR = &,
+ PROCEDURE = inet_and
+);
+
+CREATE OPERATOR + (
+ LEFTARG = inet,
+ RIGHTARG = int,
+ COMMUTATOR = +,
+ PROCEDURE = inet_plusi
+);
+
+CREATE OPERATOR + (
+ LEFTARG = int,
+ RIGHTARG = inet,
+ COMMUTATOR = +,
+ PROCEDURE = inet_iplus
+);
+
+CREATE OPERATOR - (
+ LEFTARG = inet,
+ RIGHTARG = int,
+ COMMUTATOR = -,
+ PROCEDURE = inet_mini
+);
+
+CREATE OPERATOR - (
+ LEFTARG = inet,
+ RIGHTARG = inet,
+ COMMUTATOR = -,
+ PROCEDURE = inet_min
+);
--
Sincerely, srb(at)cuci(dot)nl
Stephen R. van den Berg (AKA BuGless).

Skiing beyond this point may result in death and/or loss of skiing privileges.

Stephen R. van den Berg wrote:
> I've been meaning to send it in for a while now, IMHO it could be
> made part of the main distribution as well, but contrib is fine
> if not everyone likes it.
>
> I included the source in patch-format, since I didn't feel comfortable
> attaching a tar.gz file on this list.
> Below is an excerpt from the included README:
> -------------------------------- cut here ---------------------------------
> inet extended operations
> ~~~~~~~~~~~~~~~~~~~~~~~~
>
> This directory contains definitions for extended operators on the
> inet data type.
>
> Operators available are:
>
> ~ & | + -
>
> It supports the "natural" arithmetic with IP addresses and integers.
> It is useful for applications which have to hand out and administer
> ranges of IP-addresses (like a Radius or DHCP server).

I have modified your original patch to add the code into the main
backend routines. I added documentation and regression tests as well.

I created only inet functions because family() works on both inet and
cidr, so I assume we are to create just inet versions and cidr will
still work.

Patch needs a catalog version bump.

One unusual issue is because 'inet - inet' returns an int32, you can't
subtract two IPv6 addresses that differ by more than int32.

A larger problem is this:

test=> SELECT '255.255.255.0'::inet - '1.1.1.1'::inet;
?column?
-----------
-16843265
(1 row)

Should subtraction return int8? Perhaps it is OK because:

test=> select '255.255.255.0'::inet - (-16843265);
?column?
----------
1.1.1.1
(1 row)

We don't have an unsigned data type. Of course we also have this
excitement:

test=> SELECT '255.255.255.0'::inet + 1000000;
?column?
------------
0.15.65.64
(1 row)

so we underflow and overflow cleanly. Not great, but it works.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> A larger problem is this:

> test=> SELECT '255.255.255.0'::inet - '1.1.1.1'::inet;
> ?column?
> -----------
> -16843265
> (1 row)

> Should subtraction return int8?

Probably, and for that matter the addition operators should take int8;
on IPV6 data even that's not really wide enough.

> We don't have an unsigned data type. Of course we also have this
> excitement:

> test=> SELECT '255.255.255.0'::inet + 1000000;
> ?column?
> ------------
> 0.15.65.64
> (1 row)

> so we underflow and overflow cleanly. Not great, but it works.

"Cleanly" isn't the adjective I'd use for that. There should be an
overflow error.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > A larger problem is this:
>
> > test=> SELECT '255.255.255.0'::inet - '1.1.1.1'::inet;
> > ?column?
> > -----------
> > -16843265
> > (1 row)
>
> > Should subtraction return int8?
>
> Probably, and for that matter the addition operators should take int8;
> on IPV6 data even that's not really wide enough.
>
> > We don't have an unsigned data type. Of course we also have this
> > excitement:
>
> > test=> SELECT '255.255.255.0'::inet + 1000000;
> > ?column?
> > ------------
> > 0.15.65.64
> > (1 row)
>
> > so we underflow and overflow cleanly. Not great, but it works.
>
> "Cleanly" isn't the adjective I'd use for that. There should be an
> overflow error.

OK, changed to int8, and overflow checks added.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Patch applied. Thanks.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > A larger problem is this:
> >
> > > test=> SELECT '255.255.255.0'::inet - '1.1.1.1'::inet;
> > > ?column?
> > > -----------
> > > -16843265
> > > (1 row)
> >
> > > Should subtraction return int8?
> >
> > Probably, and for that matter the addition operators should take int8;
> > on IPV6 data even that's not really wide enough.
> >
> > > We don't have an unsigned data type. Of course we also have this
> > > excitement:
> >
> > > test=> SELECT '255.255.255.0'::inet + 1000000;
> > > ?column?
> > > ------------
> > > 0.15.65.64
> > > (1 row)
> >
> > > so we underflow and overflow cleanly. Not great, but it works.
> >
> > "Cleanly" isn't the adjective I'd use for that. There should be an
> > overflow error.
>
> OK, changed to int8, and overflow checks added.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073

> Index: doc/src/sgml/func.sgml
> ===================================================================
> RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
> retrieving revision 1.303
> diff -c -c -r1.303 func.sgml
> *** doc/src/sgml/func.sgml 26 Jan 2006 02:35:48 -0000 1.303
> --- doc/src/sgml/func.sgml 10 Feb 2006 04:11:52 -0000
> ***************
> *** 6787,6792 ****
> --- 6787,6822 ----
> <entry>contains or equals</entry>
> <entry><literal>inet '192.168.1/24' &gt;&gt;= inet '192.168.1/24'</literal></entry>
> </row>
> + <row>
> + <entry> <literal>~</literal> </entry>
> + <entry>bitwise NOT</entry>
> + <entry><literal>~ inet '192.168.1.6'</literal></entry>
> + </row>
> + <row>
> + <entry> <literal>&amp;</literal> </entry>
> + <entry>bitwise AND</entry>
> + <entry><literal>inet '192.168.1.6' &amp; inet '0.0.0.255'</literal></entry>
> + </row>
> + <row>
> + <entry> <literal>|</literal> </entry>
> + <entry>bitwise OR</entry>
> + <entry><literal>inet '192.168.1.6' | inet '0.0.0.255'</literal></entry>
> + </row>
> + <row>
> + <entry> <literal>+</literal> </entry>
> + <entry>addition</entry>
> + <entry><literal>inet '192.168.1.6' + 25</literal></entry>
> + </row>
> + <row>
> + <entry> <literal>-</literal> </entry>
> + <entry>subtraction</entry>
> + <entry><literal>inet '192.168.1.43' - 36</literal></entry>
> + </row>
> + <row>
> + <entry> <literal>-</literal> </entry>
> + <entry>subtraction</entry>
> + <entry><literal>inet '192.168.1.43' - inet '192.168.1.19'</literal></entry>
> + </row>
> </tbody>
> </tgroup>
> </table>
> Index: src/backend/utils/adt/network.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/backend/utils/adt/network.c,v
> retrieving revision 1.63
> diff -c -c -r1.63 network.c
> *** src/backend/utils/adt/network.c 7 Feb 2006 17:04:04 -0000 1.63
> --- src/backend/utils/adt/network.c 10 Feb 2006 04:11:57 -0000
> ***************
> *** 27,32 ****
> --- 27,33 ----
> static int bitncmp(void *l, void *r, int n);
> static bool addressOK(unsigned char *a, int bits, int family);
> static int ip_addrsize(inet *inetptr);
> + static Datum internal_inetpl(inet *ip, int64 iarg);
>
> /*
> * Access macros.
> ***************
> *** 1250,1252 ****
> --- 1251,1458 ----
>
> PG_RETURN_DATUM(DirectFunctionCall1(int4in, CStringGetDatum(local_port)));
> }
> +
> +
> + Datum
> + inetnot(PG_FUNCTION_ARGS)
> + {
> + inet *ip = PG_GETARG_INET_P(0);
> + inet *dst;
> +
> + dst = (inet *) palloc0(VARHDRSZ + sizeof(inet_struct));
> +
> + {
> + int nb = ip_addrsize(ip);
> + unsigned char *pip = ip_addr(ip);
> + unsigned char *pdst = ip_addr(dst);
> +
> + while (nb-- > 0)
> + pdst[nb] = ~pip[nb];
> + }
> + ip_bits(dst) = ip_bits(ip);
> +
> + ip_family(dst) = ip_family(ip);
> + VARATT_SIZEP(dst) = VARHDRSZ +
> + ((char *) ip_addr(dst) - (char *) VARDATA(dst)) +
> + ip_addrsize(dst);
> +
> + PG_RETURN_INET_P(dst);
> + }
> +
> +
> + Datum
> + inetand(PG_FUNCTION_ARGS)
> + {
> + inet *ip = PG_GETARG_INET_P(0);
> + inet *ip2 = PG_GETARG_INET_P(1);
> + inet *dst;
> +
> + dst = (inet *) palloc0(VARHDRSZ + sizeof(inet_struct));
> +
> + if (ip_family(ip) != ip_family(ip2))
> + ereport(ERROR,
> + (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> + errmsg("mismatch in address family (%d) != (%d)",
> + ip_family(ip), ip_family(ip2))));
> + else
> + {
> + int nb = ip_addrsize(ip);
> + unsigned char *pip = ip_addr(ip);
> + unsigned char *pip2 = ip_addr(ip2);
> + unsigned char *pdst = ip_addr(dst);
> +
> + while (nb-- > 0)
> + pdst[nb] = pip[nb] & pip2[nb];
> + }
> + ip_bits(dst) = Max(ip_bits(ip), ip_bits(ip2));
> +
> + ip_family(dst) = ip_family(ip);
> + VARATT_SIZEP(dst) = VARHDRSZ +
> + ((char *) ip_addr(dst) - (char *) VARDATA(dst)) +
> + ip_addrsize(dst);
> +
> + PG_RETURN_INET_P(dst);
> + }
> +
> +
> + Datum
> + inetor(PG_FUNCTION_ARGS)
> + {
> + inet *ip = PG_GETARG_INET_P(0);
> + inet *ip2 = PG_GETARG_INET_P(1);
> + inet *dst;
> +
> + dst = (inet *) palloc0(VARHDRSZ + sizeof(inet_struct));
> +
> + if (ip_family(ip) != ip_family(ip2))
> + ereport(ERROR,
> + (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> + errmsg("mismatch in address family (%d) != (%d)",
> + ip_family(ip), ip_family(ip2))));
> + else
> + {
> + int nb = ip_addrsize(ip);
> + unsigned char *pip = ip_addr(ip);
> + unsigned char *pip2 = ip_addr(ip2);
> + unsigned char *pdst = ip_addr(dst);
> +
> + while (nb-- > 0)
> + pdst[nb] = pip[nb] | pip2[nb];
> + }
> + ip_bits(dst) = Max(ip_bits(ip), ip_bits(ip2));
> +
> + ip_family(dst) = ip_family(ip);
> + VARATT_SIZEP(dst) = VARHDRSZ +
> + ((char *) ip_addr(dst) - (char *) VARDATA(dst)) +
> + ip_addrsize(dst);
> +
> + PG_RETURN_INET_P(dst);
> + }
> +
> +
> + static Datum
> + internal_inetpl(inet *ip, int64 plus)
> + {
> + inet *dst;
> +
> + dst = (inet *) palloc0(VARHDRSZ + sizeof(inet_struct));
> +
> + {
> + int nb = ip_addrsize(ip);
> + unsigned char *pip = ip_addr(ip);
> + unsigned char *pdst = ip_addr(dst);
> + int carry = 0;
> +
> + while (nb-- > 0)
> + {
> + pdst[nb] = carry = pip[nb] + plus + carry;
> + plus /= 0x100; /* process next byte */
> + carry /= 0x100; /* remove low byte */
> + /* Overflow on high byte? */
> + if (nb == 0 && (plus != 0 || carry != 0))
> + ereport(ERROR,
> + (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
> + errmsg("result out of range")));
> + }
> + }
> + ip_bits(dst) = ip_bits(ip);
> +
> + ip_family(dst) = ip_family(ip);
> + VARATT_SIZEP(dst) = VARHDRSZ +
> + ((char *) ip_addr(dst) - (char *) VARDATA(dst)) +
> + ip_addrsize(dst);
> +
> + PG_RETURN_INET_P(dst);
> + }
> +
> +
> + Datum
> + inetpl(PG_FUNCTION_ARGS)
> + {
> + inet *ip = PG_GETARG_INET_P(0);
> + int64 plus = PG_GETARG_INT64(1);
> +
> + return internal_inetpl(ip, plus);
> + }
> +
> +
> + Datum
> + inetmi_int8(PG_FUNCTION_ARGS)
> + {
> + inet *ip = PG_GETARG_INET_P(0);
> + int64 plus = PG_GETARG_INT64(1);
> +
> + return internal_inetpl(ip, -plus);
> + }
> +
> +
> + Datum
> + inetmi(PG_FUNCTION_ARGS)
> + {
> + inet *ip = PG_GETARG_INET_P(0);
> + inet *ip2 = PG_GETARG_INET_P(1);
> + int64 res = 0;
> +
> + if (ip_family(ip) != ip_family(ip2))
> + ereport(ERROR,
> + (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> + errmsg("mismatch in address family (%d) != (%d)",
> + ip_family(ip), ip_family(ip2))));
> + else
> + {
> + int nb = ip_addrsize(ip);
> + int byte = 0;
> + unsigned char *pip = ip_addr(ip);
> + unsigned char *pip2 = ip_addr(ip2);
> +
> + while (nb-- > 0)
> + {
> + /*
> + * Error if overflow on last byte. This test is tricky
> + * because if the subtraction == 128 and res is negative, or
> + * if subtraction == -128 and res is positive, the result
> + * would still fit in int64.
> + */
> + if (byte + 1 == sizeof(int64) &&
> + (pip[nb] - pip2[nb] >= 128 + (res < 0) ||
> + pip[nb] - pip2[nb] <= -128 - (res > 0)))
> + ereport(ERROR,
> + (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
> + errmsg("result out of range")));
> + if (byte >= sizeof(int64))
> + {
> + /* Error if bytes beyond int64 length differ. */
> + if (pip[nb] != pip2[nb])
> + ereport(ERROR,
> + (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
> + errmsg("result out of range")));
> + }
> + else
> + res += (int64)(pip[nb] - pip2[nb]) << (byte * 8);
> +
> + byte++;
> + }
> + }
> +
> + PG_RETURN_INT64(res);
> + }
> Index: src/include/catalog/pg_operator.h
> ===================================================================
> RCS file: /cvsroot/pgsql/src/include/catalog/pg_operator.h,v
> retrieving revision 1.138
> diff -c -c -r1.138 pg_operator.h
> *** src/include/catalog/pg_operator.h 26 Jan 2006 02:35:49 -0000 1.138
> --- src/include/catalog/pg_operator.h 10 Feb 2006 04:11:59 -0000
> ***************
> *** 653,658 ****
> --- 653,667 ----
> DATA(insert OID = 934 ( ">>=" PGNSP PGUID b f 869 869 16 932 0 0 0 0 0 network_supeq - - ));
> #define OID_INET_SUPEQ_OP 934
>
> + DATA(insert OID = 2634 ( "~" PGNSP PGUID l f 0 869 869 0 0 0 0 0 0 inetnot - - ));
> + DATA(insert OID = 2635 ( "&" PGNSP PGUID b f 869 869 869 0 0 0 0 0 0 inetand - - ));
> + DATA(insert OID = 2636 ( "|" PGNSP PGUID b f 869 869 869 0 0 0 0 0 0 inetor - - ));
> + DATA(insert OID = 2637 ( "+" PGNSP PGUID b f 869 20 869 2638 0 0 0 0 0 inetpl - - ));
> + DATA(insert OID = 2638 ( "+" PGNSP PGUID b f 20 869 869 2637 0 0 0 0 0 int8pl_inet - - ));
> + DATA(insert OID = 2639 ( "-" PGNSP PGUID b f 869 20 869 0 0 0 0 0 0 inetmi_int8 - - ));
> + DATA(insert OID = 2640 ( "-" PGNSP PGUID b f 869 869 20 0 0 0 0 0 0 inetmi - - ));
> +
> +
> /* case-insensitive LIKE hacks */
> DATA(insert OID = 1625 ( "~~*" PGNSP PGUID b f 19 25 16 0 1626 0 0 0 0 nameiclike iclikesel iclikejoinsel ));
> #define OID_NAME_ICLIKE_OP 1625
> Index: src/include/catalog/pg_proc.h
> ===================================================================
> RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
> retrieving revision 1.394
> diff -c -c -r1.394 pg_proc.h
> *** src/include/catalog/pg_proc.h 9 Feb 2006 14:53:51 -0000 1.394
> --- src/include/catalog/pg_proc.h 10 Feb 2006 04:12:03 -0000
> ***************
> *** 2431,2436 ****
> --- 2431,2451 ----
> DATA(insert OID = 2199 ( inet_server_port PGNSP PGUID 12 f f f f s 0 23 "" _null_ _null_ _null_ inet_server_port - _null_ ));
> DESCR("server's port number for this connection");
>
> + DATA(insert OID = 2627 ( inetnot PGNSP PGUID 12 f f t f i 1 869 "869" _null_ _null_ _null_ inetnot - _null_ ));
> + DESCR("binary NOT");
> + DATA(insert OID = 2628 ( inetand PGNSP PGUID 12 f f t f i 2 869 "869 869" _null_ _null_ _null_ inetand - _null_ ));
> + DESCR("binary AND");
> + DATA(insert OID = 2629 ( inetor PGNSP PGUID 12 f f t f i 2 869 "869 869" _null_ _null_ _null_ inetor - _null_ ));
> + DESCR("binary OR");
> + DATA(insert OID = 2630 ( inetpl PGNSP PGUID 12 f f t f i 2 869 "869 20" _null_ _null_ _null_ inetpl - _null_ ));
> + DESCR("add integer to INET value");
> + DATA(insert OID = 2631 ( int8pl_inet PGNSP PGUID 14 f f t f i 2 869 "20 869" _null_ _null_ _null_ "select $2 + $1" - _null_ ));
> + DESCR("add integer to INET value");
> + DATA(insert OID = 2632 ( inetmi_int8 PGNSP PGUID 12 f f t f i 2 869 "869 20" _null_ _null_ _null_ inetmi_int8 - _null_ ));
> + DESCR("subtract integer from INET value");
> + DATA(insert OID = 2633 ( inetmi PGNSP PGUID 12 f f t f i 2 20 "869 869" _null_ _null_ _null_ inetmi - _null_ ));
> + DESCR("subtract INET values");
> +
> DATA(insert OID = 1686 ( numeric PGNSP PGUID 12 f f t f i 1 1700 "25" _null_ _null_ _null_ text_numeric - _null_ ));
> DESCR("(internal)");
> DATA(insert OID = 1688 ( text PGNSP PGUID 12 f f t f i 1 25 "1700" _null_ _null_ _null_ numeric_text - _null_ ));
> Index: src/include/utils/builtins.h
> ===================================================================
> RCS file: /cvsroot/pgsql/src/include/utils/builtins.h,v
> retrieving revision 1.272
> diff -c -c -r1.272 builtins.h
> *** src/include/utils/builtins.h 26 Jan 2006 02:35:50 -0000 1.272
> --- src/include/utils/builtins.h 10 Feb 2006 04:12:04 -0000
> ***************
> *** 734,739 ****
> --- 734,745 ----
> extern Datum inet_client_port(PG_FUNCTION_ARGS);
> extern Datum inet_server_addr(PG_FUNCTION_ARGS);
> extern Datum inet_server_port(PG_FUNCTION_ARGS);
> + extern Datum inetnot(PG_FUNCTION_ARGS);
> + extern Datum inetand(PG_FUNCTION_ARGS);
> + extern Datum inetor(PG_FUNCTION_ARGS);
> + extern Datum inetpl(PG_FUNCTION_ARGS);
> + extern Datum inetmi_int8(PG_FUNCTION_ARGS);
> + extern Datum inetmi(PG_FUNCTION_ARGS);
>
> /* mac.c */
> extern Datum macaddr_in(PG_FUNCTION_ARGS);
> Index: src/test/regress/expected/inet.out
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/expected/inet.out,v
> retrieving revision 1.19
> diff -c -c -r1.19 inet.out
> *** src/test/regress/expected/inet.out 8 Oct 2004 01:45:37 -0000 1.19
> --- src/test/regress/expected/inet.out 10 Feb 2006 04:12:05 -0000
> ***************
> *** 240,244 ****
> --- 240,376 ----
> 192.168.1.0/26 | 192.168.1.226
> (6 rows)
>
> + SELECT ~i FROM inet_tbl;
> + ?column?
> + --------------------------------------------
> + 63.87.254.29/24
> + 63.87.254.29
> + 63.87.254.255/24
> + 63.87.254.255/25
> + 63.87.254.0/24
> + 63.87.254.0/25
> + 245.254.253.252/8
> + 245.254.253.252/8
> + 245.254.253.252
> + 245.254.253.252/24
> + 245.254.253.252/16
> + 245.254.253.252/8
> + 244.254.253.252/8
> + 246.254.253.252/8
> + ffef:ffdc:ffff:ffff:ffff:ffff:ffff:ff0e/64
> + ffef:ffdc:ffff:ffff:ffff:ffff:ffff:0
> + ffff:ffff:ffff:ffff:ffff:ffff:fbfc:fdfe/24
> + (17 rows)
> +
> + SELECT i & c FROM inet_tbl;
> + ?column?
> + ----------------
> + 192.168.1.0/24
> + 192.168.1.0
> + 192.168.1.0/24
> + 192.168.1.0/25
> + 192.168.1.0/24
> + 192.168.1.0/25
> + 10.0.0.0/8
> + 10.0.0.0
> + 10.1.2.3
> + 10.1.2.0/24
> + 10.1.0.0/16
> + 10.0.0.0/8
> + 10.0.0.0/8
> + 8.0.0.0/8
> + 10:23::f1
> + 10:23::8000
> + ::0.2.2.0
> + (17 rows)
> +
> + SELECT i | c FROM inet_tbl;
> + ?column?
> + ------------------
> + 192.168.1.226/24
> + 192.168.1.226
> + 192.168.1.0/24
> + 192.168.1.0/25
> + 192.168.1.255/24
> + 192.168.1.255/25
> + 10.1.2.3/8
> + 10.1.2.3
> + 10.1.2.3
> + 10.1.2.3/24
> + 10.1.2.3/16
> + 10.1.2.3/8
> + 11.1.2.3/8
> + 11.1.2.3/8
> + 10:23::f1
> + 10:23::ffff
> + ::ffff:5.3.3.5
> + (17 rows)
> +
> + SELECT i + 500 FROM inet_tbl;
> + ?column?
> + ------------------
> + 192.168.4.214/24
> + 192.168.4.214
> + 192.168.3.244/24
> + 192.168.3.244/25
> + 192.168.4.243/24
> + 192.168.4.243/25
> + 10.1.4.247/8
> + 10.1.4.247/8
> + 10.1.4.247
> + 10.1.4.247/24
> + 10.1.4.247/16
> + 10.1.4.247/8
> + 11.1.4.247/8
> + 9.1.4.247/8
> + 10:23::3e5/64
> + 10:23::1:2f3
> + ::4.3.4.245/24
> + (17 rows)
> +
> + SELECT i - 500 FROM inet_tbl;
> + ?column?
> + --------------------
> + 192.168.255.238/24
> + 192.168.255.238
> + 192.168.255.12/24
> + 192.168.255.12/25
> + 192.168.0.11/24
> + 192.168.0.11/25
> + 10.1.0.15/8
> + 10.1.0.15/8
> + 10.1.0.15
> + 10.1.0.15/24
> + 10.1.0.15/16
> + 10.1.0.15/8
> + 11.1.0.15/8
> + 9.1.0.15/8
> + 10:23::fefd/64
> + 10:23::fe0b
> + ::4.3.0.13/24
> + (17 rows)
> +
> + SELECT i - c FROM inet_tbl;
> + ?column?
> + ------------------
> + 226
> + 226
> + 0
> + 0
> + 255
> + 255
> + 66051
> + 66051
> + 0
> + 3
> + 515
> + 66051
> + 16843267
> + -16711165
> + 0
> + 32767
> + -281470631346435
> + (17 rows)
> +
> SET enable_seqscan TO on;
> DROP INDEX inet_idx1;
> Index: src/test/regress/sql/inet.sql
> ===================================================================
> RCS file: /cvsroot/pgsql/src/test/regress/sql/inet.sql,v
> retrieving revision 1.11
> diff -c -c -r1.11 inet.sql
> *** src/test/regress/sql/inet.sql 8 Oct 2004 01:45:37 -0000 1.11
> --- src/test/regress/sql/inet.sql 10 Feb 2006 04:12:05 -0000
> ***************
> *** 62,67 ****
> --- 62,75 ----
> SET enable_seqscan TO off;
> SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr;
> SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr;
> +
> + SELECT ~i FROM inet_tbl;
> + SELECT i & c FROM inet_tbl;
> + SELECT i | c FROM inet_tbl;
> + SELECT i + 500 FROM inet_tbl;
> + SELECT i - 500 FROM inet_tbl;
> + SELECT i - c FROM inet_tbl;
> +
> SET enable_seqscan TO on;
> DROP INDEX inet_idx1;
>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073