Re: Arithmetic operators for macaddr type

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

2011/12/12 Brendan Jurd <direvus(at)gmail(dot)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?

+1

Pavel

>
> Cheers,
> BJ
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On 12 December 2011 15:59, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2011/12/12 Brendan Jurd <direvus(at)gmail(dot)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

On Tue, Dec 13, 2011 at 2:16 PM, Brendan Jurd <direvus(at)gmail(dot)com> wrote:
> On 12 December 2011 15:59, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2011/12/12 Brendan Jurd <direvus(at)gmail(dot)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

On Tue, Jan 17, 2012 at 12:38 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)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