On 12 December 2011 15:59, Pavel Stehule <[email protected]> wrote:
> 2011/12/12 Brendan Jurd <[email protected]>:
>> 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
*** 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>></literal>, <literal><=</literal>, etc.) for
! lexicographical ordering.
</para>
</sect1>
--- 8300,8308 ----
<para>
The <type>macaddr</type> type also supports the standard relational
operators (<literal>></literal>, <literal><=</literal>, etc.) for
! lexicographical ordering, and the bitwise arithmetic operators
! (<literal>~</literal>, <literal>&</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 <[email protected]>
*/
*** 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;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
