contrib/xinetops for 8.1 "patch"
This is a discussion on contrib/xinetops for 8.1 "patch" within the Pgsql Patches forums, part of the PostgreSQL category; --> I've been meaning to send it in for a while now, IMHO it could be made part of the ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-18-2008, 01:02 AM
| ||||
| ||||
 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@cuci.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@cuci.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@cuci.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@cuci.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@cuci.nl Stephen R. van den Berg (AKA BuGless). Skiing beyond this point may result in death and/or loss of skiing privileges. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster      |
|
04-18-2008, 01:19 AM
| |||
| |||
| Re: contrib/xinetops for 8.1 "patch" 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@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 9 Feb 2006 20:18:40 -0000 *************** *** 6787,6792 **** --- 6787,6822 ---- <entry>contains or equals</entry> <entry><literal>inet '192.168.1/24' >>= 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>&</literal> </entry> + <entry>bitwise AND</entry> + <entry><literal>inet '192.168.1.6' & 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 9 Feb 2006 20:18:43 -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, int iarg); /* * Access macros. *************** *** 1250,1252 **** --- 1251,1441 ---- 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, int 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 & 0xff) + carry; + plus >>= 8; /* process next byte */ + carry >>= 8; /* remove low byte */ + } + } + 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); + int32 plus = PG_GETARG_INT32(1); + + return internal_inetpl(ip, plus); + } + + + Datum + inetmi_int4(PG_FUNCTION_ARGS) + { + inet *ip = PG_GETARG_INET_P(0); + int32 plus = PG_GETARG_INT32(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); + int32 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) + { + if (byte >= sizeof(int32)) + { + /* Error on IPv6 subtraction when bytes beyond int32 differ */ + if (pip[nb] != pip2[nb]) + ereport(ERROR, + (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), + errmsg("result out of range"))); + } + else + res += (pip[nb] - pip2[nb]) << (byte * 8); + + byte++; + } + } + + PG_RETURN_INT32(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 9 Feb 2006 20:18:45 -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 23 869 2638 0 0 0 0 0 inetpl - - )); + DATA(insert OID = 2638 ( "+" PGNSP PGUID b f 23 869 869 2637 0 0 0 0 0 int4pl_inet - - )); + DATA(insert OID = 2639 ( "-" PGNSP PGUID b f 869 23 869 0 0 0 0 0 0 inetmi_int4 - - )); + DATA(insert OID = 2640 ( "-" PGNSP PGUID b f 869 869 23 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 9 Feb 2006 20:18:50 -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 23" _null_ _null_ _null_ inetpl - _null_ )); + DESCR("add integer to INET value"); + DATA(insert OID = 2631 ( int4pl_inet PGNSP PGUID 14 f f t f i 2 869 "23 869" _null_ _null_ _null_ "select $2 + $1" - _null_ )); + DESCR("add integer to INET value"); + DATA(insert OID = 2632 ( inetmi_int4 PGNSP PGUID 12 f f t f i 2 869 "869 23" _null_ _null_ _null_ inetmi_int4 - _null_ )); + DESCR("subtract integer from INET value"); + DATA(insert OID = 2633 ( inetmi PGNSP PGUID 12 f f t f i 2 23 "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 9 Feb 2006 20:18:51 -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_int4(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 9 Feb 2006 20:18:52 -0000 *************** *** 240,244 **** --- 240,381 ---- 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 + 5000 FROM inet_tbl; + ?column? + ------------------- + 192.168.21.106/24 + 192.168.21.106 + 192.168.20.136/24 + 192.168.20.136/25 + 192.168.21.135/24 + 192.168.21.135/25 + 10.1.21.139/8 + 10.1.21.139/8 + 10.1.21.139 + 10.1.21.139/24 + 10.1.21.139/16 + 10.1.21.139/8 + 11.1.21.139/8 + 9.1.21.139/8 + 10:23::1479/64 + 10:23::1:1387 + ::4.3.21.137/24 + (17 rows) + + SELECT i - 5000 FROM inet_tbl; + ?column? + ---------------------------------------- + 192.167.238.90/24 + 192.167.238.90 + 192.167.237.120/24 + 192.167.237.120/25 + 192.167.238.119/24 + 192.167.238.119/25 + 10.0.238.123/8 + 10.0.238.123/8 + 10.0.238.123 + 10.0.238.123/24 + 10.0.238.123/16 + 10.0.238.123/8 + 11.0.238.123/8 + 9.0.238.123/8 + 10:22:ffff:ffff:ffff:ffff:ffff:ed69/64 + 10:23::ec77 + ::4.2.238.121/24 + (17 rows) + + SELECT i - c FROM inet_tbl where family(i) = 4; + ?column? + ----------- + 226 + 226 + 0 + 0 + 255 + 255 + 66051 + 66051 + 0 + 3 + 515 + 66051 + 16843267 + -16711165 + (14 rows) + + SELECT (inet '::ffff:1.2.3.4/128') - (inet '::ffff:1.1.1.1/128'); + ?column? + ---------- + 66051 + (1 row) + + SELECT (inet '::ffff:1.1.1.1/128') - (inet '::1/128'); + ERROR: result out of range 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 9 Feb 2006 20:18:52 -0000 *************** *** 62,67 **** --- 62,77 ---- 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 + 5000 FROM inet_tbl; + SELECT i - 5000 FROM inet_tbl; + SELECT i - c FROM inet_tbl where family(i) = 4; + SELECT (inet '::ffff:1.2.3.4/128') - (inet '::ffff:1.1.1.1/128'); + SELECT (inet '::ffff:1.1.1.1/128') - (inet '::1/128'); + SET enable_seqscan TO on; DROP INDEX inet_idx1; ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org |
|
04-18-2008, 01:19 AM
| |||
| |||
| Re: contrib/xinetops for 8.1 "patch" 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. regards, tom lane ---------------------------(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 |
|
04-18-2008, 01:19 AM
| |||
| |||
| Re: contrib/xinetops for 8.1 "patch" 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' >>= 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>&</literal> </entry> + <entry>bitwise AND</entry> + <entry><literal>inet '192.168.1.6' & 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 |
|
04-18-2008, 01:19 AM
| ||||
| ||||
| Re: contrib/xinetops for 8.1 "patch" Patch applied. Thanks. --------------------------------------------------------------------------- Bruce Momjian wrote: > 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' >>= 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>&</literal> </entry> > + <entry>bitwise AND</entry> > + <entry><literal>inet '192.168.1.6' & 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@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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings |
 |
« Re: [COMMITTERS] pgsql: Allow psql multi-line column values to align
|
Re: [COMMITTERS] pgsql: Allow VACUUM to complete faster by avoiding »
| Thread Tools | |
| Display Modes | |
Linear Mode
|
|
