
Dear hackers,

still in the spirit of "it may be useful to others, as it was to me, and
it does cost very little", and before submitting a small patch and being
exploded because it is obviously very stupid:

Would it be appropriate to contribute BIT_AND and BIT_OR aggregates
for integer types, with some documentation and minimal validation?
There has been a discussion recently on pgsql-general about that.

1) mysql has them... it seems to be an argument here around sometimes;-)
   it is in their proud list-of-features that it has and that postgresql
   does not have.

2) each declaration is a 4-line "CREATE AGGREGATE", the underlying
   functions being already available for & and | operators.

3) I know that one can add them if they are needed, but what
   would be the point of NOT providing such simple features, and
   having the basic user to have to learn about creating aggregate
   functions and browse a long time in the documentation for that?

I needed them for some application: I'm happy I know how to add them now,
but I would have been even happier if I had found them just available
without having to learn about these intesting details about postgresql
extensions.

Have a nice day,

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Tue, 27 Apr 2004 18:24:56 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
bitwise and/or aggregate functions?
Fabien COELHO wrote:
> 
> Dear hackers,
> 
> still in the spirit of "it may be useful to others, as it was to me, and
> it does cost very little", and before submitting a small patch and being
> exploded because it is obviously very stupid:
> 
> Would it be appropriate to contribute BIT_AND and BIT_OR aggregates
> for integer types, with some documentation and minimal validation?
> There has been a discussion recently on pgsql-general about that.
> 
> 1) mysql has them... it seems to be an argument here around sometimes;-)
>    it is in their proud list-of-features that it has and that postgresql
>    does not have.
> 
> 2) each declaration is a 4-line "CREATE AGGREGATE", the underlying
>    functions being already available for & and | operators.
> 
> 3) I know that one can add them if they are needed, but what
>    would be the point of NOT providing such simple features, and
>    having the basic user to have to learn about creating aggregate
>    functions and browse a long time in the documentation for that?
> 
> I needed them for some application: I'm happy I know how to add them now,
> but I would have been even happier if I had found them just available
> without having to learn about these intesting details about postgresql
> extensions.

I am confused why you would use bit on integers when there is a bit type
with an AND operator:

 pg_catalog | &    | bit        | bit        | bit         | bitwise and

-- 
  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

Tue, 27 Apr 2004 13:51:26 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: bitwise and/or aggregate functions?

> > Would it be appropriate to contribute BIT_AND and BIT_OR aggregates
>
> I am confused why you would use bit on integers

Well, (I think) I need them to manipulate pg_catalog's aclitem bitfields.
I plea not guilty for the design of pg_catalog;-)
Moreover, I added aclitem accessors which return INT4 in a recent patch
that you kindly applied.


> when there is a bit type with an AND operator:
>  pg_catalog | &    | bit        | bit        | bit         | bitwise and

Sure. "&" is also available for all integer types.
BTW, I'm arguing about AGGREGATE functions, and there is no aggregate
functions at the time, neither for int* nor for bit.

SELECT BIT_OR(aclitem_privs(...)) AS effective_privs
FROM ...
WHERE aclitem_grantee(...)=... AND ... ;


Or maybe I cannot understand why you're confused?

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Wed, 28 Apr 2004 08:46:09 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: bitwise and/or aggregate functions?
> SELECT BIT_OR(aclitem_privs(...)) AS effective_privs
> FROM ...
> WHERE aclitem_grantee(...)=... AND ... ;

Is there anything in SQL2003 about such operators?  If there is, we 
should make sure we use the correct aggregate names.

Chris


Wed, 28 Apr 2004 15:07:59 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: bitwise and/or aggregate functions?

> > SELECT BIT_OR(aclitem_privs(...)) AS effective_privs
> > FROM ...
> > WHERE aclitem_grantee(...)=... AND ... ;
>
> Is there anything in SQL2003 about such operators?  If there is, we
> should make sure we use the correct aggregate names.

That's a point!

I thought of BIT_* because it is short and also used by mysql.
Ingres has BIT_AND and BIT_OR functions, but they are not aggregates.

I don't know where these standards are available online... It seems they
are not available:-(

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Wed, 28 Apr 2004 10:08:07 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: bitwise and/or aggregate functions?
>>Is there anything in SQL2003 about such operators?  If there is, we
>>should make sure we use the correct aggregate names.
> 
> That's a point!
> 
> I thought of BIT_* because it is short and also used by mysql.
> Ingres has BIT_AND and BIT_OR functions, but they are not aggregates.
> 
> I don't know where these standards are available online... It seems they
> are not available:-(

Neil - can you check your SQL2003 copy to see if it mentions standard 
aggregates on bit types?

Thanks,

Chris


Wed, 28 Apr 2004 16:18:24 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: bitwise and/or aggregate functions?

> > I thought of BIT_* because it is short and also used by mysql.
> > Ingres has BIT_AND and BIT_OR functions, but they are not aggregates.
> >
> > I don't know where these standards are available online... It seems they
> > are not available:-(
>
> Neil - can you check your SQL2003 copy to see if it mentions standard
> aggregates on bit types?

I haven't seen such a function in the "draft" standard I have found.
It seems it is not yet available from INCITS.

There are also EVERY (= aggregate AND) and SOME/ANY (= aggregate OR) for
booleans, that are interesting. I'll add them with the patch. However I
think that I may need to add two small functions for that.

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Wed, 28 Apr 2004 11:58:16 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: bitwise and/or aggregate functions?
On Tue, May 18, 2004 at 12:39:08PM -0400, Neil Conway wrote:

> A copy that claims to "represent an almost indistinuishable delta on the 
> actual SQL 2003 database standard" is available online here:
> 
> http://www.wiscorp.com/sql/sql_2003_standard.zip

Those are PDFs AFAIR, not easily greppable ... do you have a text version,
or do you always look up things by looking at the TOC?  I'm not thrilled
with the idea of reading all 1500 pages of it ...

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"El dï¿½a que dejes de cambiar dejarï¿½s de vivir"

Tue, 18 May 2004 09:08:49 -0400
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl>
Re: bitwise and/or aggregate functions?
[ Sorry for the latency of my response, Chris -- this got buried in my 
inbox... ]

Fabien COELHO wrote:
>>>I don't know where these standards are available online... It seems they
>>>are not available:-(

A copy that claims to "represent an almost indistinuishable delta on the 
actual SQL 2003 database standard" is available online here:

http://www.wiscorp.com/sql/sql_2003_standard.zip

>>Neil - can you check your SQL2003 copy to see if it mentions standard
>>aggregates on bit types?

I couldn't see any mention of any aggregates specific to the bit types, 
although my ability to accurately divine information from the standard 
has been less than perfect in the past. There are the EVERY() and ANY() 
aggregates that Fabien mentioned, though.

-Neil


Tue, 18 May 2004 12:39:08 -0400
Neil Conway <neilc@samurai.com>
Re: bitwise and/or aggregate functions?
Alvaro Herrera Munoz wrote:
> Those are PDFs AFAIR, not easily greppable

Not greppable, but any half-decent PDF viewer should have a "search" 
feature that should allow much the same thing. Checking the index is 
another way to go, although it is somewhat time-consuming.

I don't have access to an ASCII version (of SQL2003; I believe I've got 
an ASCII copy of SQL92 around here somewhere).

-Neil


Tue, 18 May 2004 13:53:03 -0400
Neil Conway <neilc@samurai.com>
Re: bitwise and/or aggregate functions?
Neil Conway <neilc@samurai.com> writes:
> Fabien COELHO wrote:
>> Neil - can you check your SQL2003 copy to see if it mentions standard
>> aggregates on bit types?

> I couldn't see any mention of any aggregates specific to the bit types, 

There certainly are none, since in fact SQL2003 removes the BIT types
entirely.  See Annex E:

2) ISO/IEC 9075-2:1999 defined data types called BIT and BIT VARYING.
   These data types have been deleted from this edition of ISO/IEC 9075.

			regards, tom lane

Tue, 18 May 2004 20:38:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: bitwise and/or aggregate functions? 
Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
> > Fabien COELHO wrote:
> >> Neil - can you check your SQL2003 copy to see if it mentions standard
> >> aggregates on bit types?
> 
> > I couldn't see any mention of any aggregates specific to the bit types, 
> 
> There certainly are none, since in fact SQL2003 removes the BIT types
> entirely.  See Annex E:
> 
> 2) ISO/IEC 9075-2:1999 defined data types called BIT and BIT VARYING.
>    These data types have been deleted from this edition of ISO/IEC 9075.

Understand.  To me, allowing bitwise and boolean aggregates on a column
seemed like a natural capability we should have.

-- 
  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

Tue, 18 May 2004 21:16:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: bitwise and/or aggregate functions?