Thread: 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@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? +1 Pavel > > 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
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
Attachment
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
Attachment
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