Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.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@candle.pha.pa.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

Reply via email to