Arithmetic operators for macaddr type

Started by Brendan Jurdabout 14 years ago5 messages

direvus@gmail.com

about 14 years ago

Hello folks,

I just bumped into a situation where I wanted to do a little macaddr
arithmetic in postgres. I note that the inet type has support for
bitwise AND, OR and NOT, as well as subtraction, but macaddr has none
of the above.

These operations are easy to perform in C, but relatively a pain to do
in SQL, especially as there doesn't seem to be a direct way to get a
macaddr into a plain numeric form.

I can't see any reason why postgres shouldn't support these operations
on macaddr. I'd like to add them as fully realised operators in core.
Would that be acceptable?

Cheers,
BJ

Pavel Stehule

pavel.stehule@gmail.com

about 14 years ago

In reply to: Brendan Jurd (#1)

Re: Arithmetic operators for macaddr type

2011/12/12 Brendan Jurd <direvus@gmail.com>:

Hello folks,

I just bumped into a situation where I wanted to do a little macaddr
arithmetic in postgres. I note that the inet type has support for
bitwise AND, OR and NOT, as well as subtraction, but macaddr has none
of the above.

These operations are easy to perform in C, but relatively a pain to do
in SQL, especially as there doesn't seem to be a direct way to get a
macaddr into a plain numeric form.

I can't see any reason why postgres shouldn't support these operations
on macaddr. I'd like to add them as fully realised operators in core.
Would that be acceptable?

Pavel

Show quoted text

Cheers,
BJ

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Brendan Jurd

direvus@gmail.com

about 14 years ago

In reply to: Pavel Stehule (#2)

1 attachment(s)

Re: Arithmetic operators for macaddr type

On 12 December 2011 15:59, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2011/12/12 Brendan Jurd <direvus@gmail.com>:

I just bumped into a situation where I wanted to do a little macaddr
arithmetic in postgres. I note that the inet type has support for
bitwise AND, OR and NOT, as well as subtraction, but macaddr has none
of the above.

+1

Here is a patch for $SUBJECT. I merely added support for ~, & and |
operators for the macaddr type. The patch itself is rather trivial,
and includes regression tests and a doc update.

For the documentation, I did think about adding a new table for the
macaddr operators, but in the end decided it would probably be an
overkill. If others think a table would be better, I'm happy to
revise it.

I also considered adding a function which would return the numeric
value of the MAC as a bigint, but figured I might save that for a
separate patch.

Cheers,
BJ

Attachments:

macaddr-bitwise-ops.difftext/x-patch; charset=US-ASCII; name=macaddr-bitwise-ops.diffDownload

*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 8300,8306 **** CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
     <para>
      The <type>macaddr</type> type also supports the standard relational
      operators (<literal>&gt;</literal>, <literal>&lt;=</literal>, etc.) for
!     lexicographical ordering.
     </para>
  
    </sect1>
--- 8300,8308 ----
     <para>
      The <type>macaddr</type> type also supports the standard relational
      operators (<literal>&gt;</literal>, <literal>&lt;=</literal>, etc.) for
!     lexicographical ordering, and the bitwise arithmetic operators
!     (<literal>~</literal>, <literal>&amp;</literal> and <literal>|</literal>)
!     for NOT, AND and OR.
     </para>
  
    </sect1>
*** a/src/backend/utils/adt/mac.c
--- b/src/backend/utils/adt/mac.c
***************
*** 242,247 **** hashmacaddr(PG_FUNCTION_ARGS)
--- 242,300 ----
  }
  
  /*
+  * Arithmetic functions: bitwise NOT, AND, OR.
+  */
+ Datum
+ macaddr_not(PG_FUNCTION_ARGS)
+ {
+ 	macaddr	   *addr = PG_GETARG_MACADDR_P(0);
+ 	macaddr	   *result;
+ 
+ 	result = (macaddr *) palloc(sizeof(macaddr));
+ 	result->a = ~addr->a;
+ 	result->b = ~addr->b;
+ 	result->c = ~addr->c;
+ 	result->d = ~addr->d;
+ 	result->e = ~addr->e;
+ 	result->f = ~addr->f;
+ 	PG_RETURN_MACADDR_P(result);
+ }
+ 
+ Datum
+ macaddr_and(PG_FUNCTION_ARGS)
+ {
+ 	macaddr	   *addr1 = PG_GETARG_MACADDR_P(0);
+ 	macaddr	   *addr2 = PG_GETARG_MACADDR_P(1);
+ 	macaddr	   *result;
+ 
+ 	result = (macaddr *) palloc(sizeof(macaddr));
+ 	result->a = addr1->a & addr2->a;
+ 	result->b = addr1->b & addr2->b;
+ 	result->c = addr1->c & addr2->c;
+ 	result->d = addr1->d & addr2->d;
+ 	result->e = addr1->e & addr2->e;
+ 	result->f = addr1->f & addr2->f;
+ 	PG_RETURN_MACADDR_P(result);
+ }
+ 
+ Datum
+ macaddr_or(PG_FUNCTION_ARGS)
+ {
+ 	macaddr	   *addr1 = PG_GETARG_MACADDR_P(0);
+ 	macaddr	   *addr2 = PG_GETARG_MACADDR_P(1);
+ 	macaddr	   *result;
+ 
+ 	result = (macaddr *) palloc(sizeof(macaddr));
+ 	result->a = addr1->a | addr2->a;
+ 	result->b = addr1->b | addr2->b;
+ 	result->c = addr1->c | addr2->c;
+ 	result->d = addr1->d | addr2->d;
+ 	result->e = addr1->e | addr2->e;
+ 	result->f = addr1->f | addr2->f;
+ 	PG_RETURN_MACADDR_P(result);
+ }
+ 
+ /*
   *	Truncation function to allow comparing mac manufacturers.
   *	From suggestion by Alex Pilosov <alex@pilosoft.com>
   */
*** a/src/include/catalog/pg_operator.h
--- b/src/include/catalog/pg_operator.h
***************
*** 1116,1121 **** DESCR("greater than");
--- 1116,1128 ----
  DATA(insert OID = 1225 (  ">="	   PGNSP PGUID b f f 829 829	 16 1223 1222 macaddr_ge scalargtsel scalargtjoinsel ));
  DESCR("greater than or equal");
  
+ DATA(insert OID = 3141 (  "~"	   PGNSP PGUID l f f	  0 829 829 0 0 macaddr_not - - ));
+ DESCR("bitwise not");
+ DATA(insert OID = 3142 (  "&"	   PGNSP PGUID b f f	829 829 829 0 0 macaddr_and - - ));
+ DESCR("bitwise and");
+ DATA(insert OID = 3143 (  "|"	   PGNSP PGUID b f f	829 829 829 0 0 macaddr_or - - ));
+ DESCR("bitwise or");
+ 
  /* INET type (these also support CIDR via implicit cast) */
  DATA(insert OID = 1201 (  "="	   PGNSP PGUID b t t 869 869	 16 1201 1202 network_eq eqsel eqjoinsel ));
  DESCR("equal");
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2037,2042 **** DATA(insert OID = 834 (  macaddr_ge			PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16
--- 2037,2045 ----
  DATA(insert OID = 835 (  macaddr_ne			PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "829 829" _null_ _null_ _null_ _null_	macaddr_ne _null_ _null_ _null_ ));
  DATA(insert OID = 836 (  macaddr_cmp		PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 23 "829 829" _null_ _null_ _null_ _null_	macaddr_cmp _null_ _null_ _null_ ));
  DESCR("less-equal-greater");
+ DATA(insert OID = 3138 (  macaddr_not		PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 829 "829" _null_ _null_ _null_ _null_	macaddr_not _null_ _null_ _null_ ));
+ DATA(insert OID = 3139 (  macaddr_and		PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 829 "829 829" _null_ _null_ _null_ _null_	macaddr_and _null_ _null_ _null_ ));
+ DATA(insert OID = 3140 (  macaddr_or		PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 829 "829 829" _null_ _null_ _null_ _null_	macaddr_or _null_ _null_ _null_ ));
  
  /* for inet type support */
  DATA(insert OID = 910 (  inet_in			PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 869 "2275" _null_ _null_ _null_ _null_ inet_in _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 892,897 **** extern Datum macaddr_eq(PG_FUNCTION_ARGS);
--- 892,900 ----
  extern Datum macaddr_ge(PG_FUNCTION_ARGS);
  extern Datum macaddr_gt(PG_FUNCTION_ARGS);
  extern Datum macaddr_ne(PG_FUNCTION_ARGS);
+ extern Datum macaddr_not(PG_FUNCTION_ARGS);
+ extern Datum macaddr_and(PG_FUNCTION_ARGS);
+ extern Datum macaddr_or(PG_FUNCTION_ARGS);
  extern Datum macaddr_trunc(PG_FUNCTION_ARGS);
  extern Datum hashmacaddr(PG_FUNCTION_ARGS);
  
*** a/src/test/regress/expected/macaddr.out
--- b/src/test/regress/expected/macaddr.out
***************
*** 103,106 **** SELECT b <> '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- false
--- 103,154 ----
   f
  (1 row)
  
+ SELECT ~b                       FROM macaddr_data;
+      ?column?      
+ -------------------
+  f7:ff:d4:fe:fd:fc
+  f7:ff:d4:fe:fd:fc
+  f7:ff:d4:fe:fd:fc
+  f7:ff:d4:fe:fd:fc
+  f7:ff:d4:fe:fd:fc
+  f7:ff:d4:fe:fd:fc
+  f7:ff:d4:fe:fd:fb
+  f7:ff:d4:fe:fd:fd
+  f7:ff:d5:fe:fd:fc
+  f7:ff:d3:fe:fd:fc
+  f7:ff:d5:fe:fd:fb
+ (11 rows)
+ 
+ SELECT  b & '00:00:00:ff:ff:ff' FROM macaddr_data;
+      ?column?      
+ -------------------
+  00:00:00:01:02:03
+  00:00:00:01:02:03
+  00:00:00:01:02:03
+  00:00:00:01:02:03
+  00:00:00:01:02:03
+  00:00:00:01:02:03
+  00:00:00:01:02:04
+  00:00:00:01:02:02
+  00:00:00:01:02:03
+  00:00:00:01:02:03
+  00:00:00:01:02:04
+ (11 rows)
+ 
+ SELECT  b | '01:02:03:04:05:06' FROM macaddr_data;
+      ?column?      
+ -------------------
+  09:02:2b:05:07:07
+  09:02:2b:05:07:07
+  09:02:2b:05:07:07
+  09:02:2b:05:07:07
+  09:02:2b:05:07:07
+  09:02:2b:05:07:07
+  09:02:2b:05:07:06
+  09:02:2b:05:07:06
+  09:02:2b:05:07:07
+  09:02:2f:05:07:07
+  09:02:2b:05:07:06
+ (11 rows)
+ 
  DROP TABLE macaddr_data;
*** a/src/test/regress/sql/macaddr.sql
--- b/src/test/regress/sql/macaddr.sql
***************
*** 35,38 **** SELECT b =  '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- true
--- 35,42 ----
  SELECT b <> '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true
  SELECT b <> '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- false
  
+ SELECT ~b                       FROM macaddr_data;
+ SELECT  b & '00:00:00:ff:ff:ff' FROM macaddr_data;
+ SELECT  b | '01:02:03:04:05:06' FROM macaddr_data;
+ 
  DROP TABLE macaddr_data;

Fujii Masao

masao.fujii@gmail.com

almost 14 years ago

In reply to: Brendan Jurd (#3)

1 attachment(s)

Re: Arithmetic operators for macaddr type

On Tue, Dec 13, 2011 at 2:16 PM, Brendan Jurd <direvus@gmail.com> wrote:

On 12 December 2011 15:59, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2011/12/12 Brendan Jurd <direvus@gmail.com>:

I just bumped into a situation where I wanted to do a little macaddr
arithmetic in postgres. I note that the inet type has support for
bitwise AND, OR and NOT, as well as subtraction, but macaddr has none
of the above.

+1

Here is a patch for $SUBJECT. I merely added support for ~, & and |
operators for the macaddr type. The patch itself is rather trivial,
and includes regression tests and a doc update.

The patch looks fine except that it uses the OIDs already used in pg_proc.h.
Attached is the updated version of the patch, which fixes the above problem.

For the documentation, I did think about adding a new table for the
macaddr operators, but in the end decided it would probably be an
overkill.

Agreed.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Attachments:

macaddr-bitwise-ops_v2.difftext/x-diff; charset=US-ASCII; name=macaddr-bitwise-ops_v2.diffDownload

*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 8300,8306 **** CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
     <para>
      The <type>macaddr</type> type also supports the standard relational
      operators (<literal>&gt;</literal>, <literal>&lt;=</literal>, etc.) for
!     lexicographical ordering.
     </para>
  
    </sect1>
--- 8300,8308 ----
     <para>
      The <type>macaddr</type> type also supports the standard relational
      operators (<literal>&gt;</literal>, <literal>&lt;=</literal>, etc.) for
!     lexicographical ordering, and the bitwise arithmetic operators
!     (<literal>~</literal>, <literal>&amp;</literal> and <literal>|</literal>)
!     for NOT, AND and OR.
     </para>
  
    </sect1>
*** a/src/backend/utils/adt/mac.c
--- b/src/backend/utils/adt/mac.c
***************
*** 242,247 **** hashmacaddr(PG_FUNCTION_ARGS)
--- 242,300 ----
  }
  
  /*
+  * Arithmetic functions: bitwise NOT, AND, OR.
+  */
+ Datum
+ macaddr_not(PG_FUNCTION_ARGS)
+ {
+ 	macaddr	   *addr = PG_GETARG_MACADDR_P(0);
+ 	macaddr	   *result;
+ 
+ 	result = (macaddr *) palloc(sizeof(macaddr));
+ 	result->a = ~addr->a;
+ 	result->b = ~addr->b;
+ 	result->c = ~addr->c;
+ 	result->d = ~addr->d;
+ 	result->e = ~addr->e;
+ 	result->f = ~addr->f;
+ 	PG_RETURN_MACADDR_P(result);
+ }
+ 
+ Datum
+ macaddr_and(PG_FUNCTION_ARGS)
+ {
+ 	macaddr	   *addr1 = PG_GETARG_MACADDR_P(0);
+ 	macaddr	   *addr2 = PG_GETARG_MACADDR_P(1);
+ 	macaddr	   *result;
+ 
+ 	result = (macaddr *) palloc(sizeof(macaddr));
+ 	result->a = addr1->a & addr2->a;
+ 	result->b = addr1->b & addr2->b;
+ 	result->c = addr1->c & addr2->c;
+ 	result->d = addr1->d & addr2->d;
+ 	result->e = addr1->e & addr2->e;
+ 	result->f = addr1->f & addr2->f;
+ 	PG_RETURN_MACADDR_P(result);
+ }
+ 
+ Datum
+ macaddr_or(PG_FUNCTION_ARGS)
+ {
+ 	macaddr	   *addr1 = PG_GETARG_MACADDR_P(0);
+ 	macaddr	   *addr2 = PG_GETARG_MACADDR_P(1);
+ 	macaddr	   *result;
+ 
+ 	result = (macaddr *) palloc(sizeof(macaddr));
+ 	result->a = addr1->a | addr2->a;
+ 	result->b = addr1->b | addr2->b;
+ 	result->c = addr1->c | addr2->c;
+ 	result->d = addr1->d | addr2->d;
+ 	result->e = addr1->e | addr2->e;
+ 	result->f = addr1->f | addr2->f;
+ 	PG_RETURN_MACADDR_P(result);
+ }
+ 
+ /*
   *	Truncation function to allow comparing mac manufacturers.
   *	From suggestion by Alex Pilosov <alex@pilosoft.com>
   */
*** a/src/include/catalog/pg_operator.h
--- b/src/include/catalog/pg_operator.h
***************
*** 1116,1121 **** DESCR("greater than");
--- 1116,1128 ----
  DATA(insert OID = 1225 (  ">="	   PGNSP PGUID b f f 829 829	 16 1223 1222 macaddr_ge scalargtsel scalargtjoinsel ));
  DESCR("greater than or equal");
  
+ DATA(insert OID = 3141 (  "~"	   PGNSP PGUID l f f	  0 829 829 0 0 macaddr_not - - ));
+ DESCR("bitwise not");
+ DATA(insert OID = 3142 (  "&"	   PGNSP PGUID b f f	829 829 829 0 0 macaddr_and - - ));
+ DESCR("bitwise and");
+ DATA(insert OID = 3143 (  "|"	   PGNSP PGUID b f f	829 829 829 0 0 macaddr_or - - ));
+ DESCR("bitwise or");
+ 
  /* INET type (these also support CIDR via implicit cast) */
  DATA(insert OID = 1201 (  "="	   PGNSP PGUID b t t 869 869	 16 1201 1202 network_eq eqsel eqjoinsel ));
  DESCR("equal");
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2039,2044 **** DATA(insert OID = 834 (  macaddr_ge			PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16
--- 2039,2047 ----
  DATA(insert OID = 835 (  macaddr_ne			PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 16 "829 829" _null_ _null_ _null_ _null_	macaddr_ne _null_ _null_ _null_ ));
  DATA(insert OID = 836 (  macaddr_cmp		PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 23 "829 829" _null_ _null_ _null_ _null_	macaddr_cmp _null_ _null_ _null_ ));
  DESCR("less-equal-greater");
+ DATA(insert OID = 3144 (  macaddr_not		PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 829 "829" _null_ _null_ _null_ _null_	macaddr_not _null_ _null_ _null_ ));
+ DATA(insert OID = 3145 (  macaddr_and		PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 829 "829 829" _null_ _null_ _null_ _null_	macaddr_and _null_ _null_ _null_ ));
+ DATA(insert OID = 3146 (  macaddr_or		PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 829 "829 829" _null_ _null_ _null_ _null_	macaddr_or _null_ _null_ _null_ ));
  
  /* for inet type support */
  DATA(insert OID = 910 (  inet_in			PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 869 "2275" _null_ _null_ _null_ _null_ inet_in _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 900,905 **** extern Datum macaddr_eq(PG_FUNCTION_ARGS);
--- 900,908 ----
  extern Datum macaddr_ge(PG_FUNCTION_ARGS);
  extern Datum macaddr_gt(PG_FUNCTION_ARGS);
  extern Datum macaddr_ne(PG_FUNCTION_ARGS);
+ extern Datum macaddr_not(PG_FUNCTION_ARGS);
+ extern Datum macaddr_and(PG_FUNCTION_ARGS);
+ extern Datum macaddr_or(PG_FUNCTION_ARGS);
  extern Datum macaddr_trunc(PG_FUNCTION_ARGS);
  extern Datum hashmacaddr(PG_FUNCTION_ARGS);
  
*** a/src/test/regress/expected/macaddr.out
--- b/src/test/regress/expected/macaddr.out
***************
*** 103,106 **** SELECT b <> '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- false
--- 103,154 ----
   f
  (1 row)
  
+ SELECT ~b                       FROM macaddr_data;
+      ?column?      
+ -------------------
+  f7:ff:d4:fe:fd:fc
+  f7:ff:d4:fe:fd:fc
+  f7:ff:d4:fe:fd:fc
+  f7:ff:d4:fe:fd:fc
+  f7:ff:d4:fe:fd:fc
+  f7:ff:d4:fe:fd:fc
+  f7:ff:d4:fe:fd:fb
+  f7:ff:d4:fe:fd:fd
+  f7:ff:d5:fe:fd:fc
+  f7:ff:d3:fe:fd:fc
+  f7:ff:d5:fe:fd:fb
+ (11 rows)
+ 
+ SELECT  b & '00:00:00:ff:ff:ff' FROM macaddr_data;
+      ?column?      
+ -------------------
+  00:00:00:01:02:03
+  00:00:00:01:02:03
+  00:00:00:01:02:03
+  00:00:00:01:02:03
+  00:00:00:01:02:03
+  00:00:00:01:02:03
+  00:00:00:01:02:04
+  00:00:00:01:02:02
+  00:00:00:01:02:03
+  00:00:00:01:02:03
+  00:00:00:01:02:04
+ (11 rows)
+ 
+ SELECT  b | '01:02:03:04:05:06' FROM macaddr_data;
+      ?column?      
+ -------------------
+  09:02:2b:05:07:07
+  09:02:2b:05:07:07
+  09:02:2b:05:07:07
+  09:02:2b:05:07:07
+  09:02:2b:05:07:07
+  09:02:2b:05:07:07
+  09:02:2b:05:07:06
+  09:02:2b:05:07:06
+  09:02:2b:05:07:07
+  09:02:2f:05:07:07
+  09:02:2b:05:07:06
+ (11 rows)
+ 
  DROP TABLE macaddr_data;
*** a/src/test/regress/sql/macaddr.sql
--- b/src/test/regress/sql/macaddr.sql
***************
*** 35,38 **** SELECT b =  '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- true
--- 35,42 ----
  SELECT b <> '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true
  SELECT b <> '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- false
  
+ SELECT ~b                       FROM macaddr_data;
+ SELECT  b & '00:00:00:ff:ff:ff' FROM macaddr_data;
+ SELECT  b | '01:02:03:04:05:06' FROM macaddr_data;
+ 
  DROP TABLE macaddr_data;

Robert Haas

robertmhaas@gmail.com

almost 14 years ago

In reply to: Fujii Masao (#4)

Re: Arithmetic operators for macaddr type

On Tue, Jan 17, 2012 at 12:38 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

Here is a patch for $SUBJECT. I merely added support for ~, & and |
operators for the macaddr type. The patch itself is rather trivial,
and includes regression tests and a doc update.

The patch looks fine except that it uses the OIDs already used in pg_proc.h.
Attached is the updated version of the patch, which fixes the above problem.

That same problem came back into existence, so I fixed it again, added
a catversion bump, and committed this.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company