Thread: new aggregate functions v3
Dear patchers, please find attached my third patch submission for adding new aggregate functions. This new version adds a note and some indexes in the documentation about sql standard names for bool_and and bool_or, as suggested by Robert Treat. It also fixes conflicts created by the removal of shameful aclitem accessors. The added aggregates are: (1) boolean-and and boolean-or aggregates named bool_and and bool_or. they (SHOULD;-) correspond to standard sql every and some/any aggregates. they do not have the right name as there is a problem with the standard and the parser for some/any. Tom also think that the standard name is misleading because NULL are ignored. (2) bitwise integer aggregates named bit_and and bit_or for int2, int4, int8 and bit types. They are not standard, but I find them useful. I needed them once. The patches adds: - 2 new very short strict functions for boolean aggregates in src/backed/utils/adt/bool.c, src/include/utils/builtins.h and src/include/catalog/pg_proc.h - the new aggregates declared in src/include/catalog/pg_proc.h and src/include/catalog/pg_aggregate.h - some documentation and validation about these new aggregates. It also updates the catalog version. It validates for me. Have a nice day, -- Fabien Coelho - coelho@cri.ensmp.fr
Attachment
[ Use newest version.] Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --------------------------------------------------------------------------- Fabien COELHO wrote: > > Dear patchers, > > please find attached my third patch submission for adding new aggregate > functions. This new version adds a note and some indexes in the > documentation about sql standard names for bool_and and bool_or, as > suggested by Robert Treat. It also fixes conflicts created by the removal > of shameful aclitem accessors. > > The added aggregates are: > > (1) boolean-and and boolean-or aggregates named bool_and and bool_or. > they (SHOULD;-) correspond to standard sql every and some/any aggregates. > they do not have the right name as there is a problem with > the standard and the parser for some/any. Tom also think that > the standard name is misleading because NULL are ignored. > > (2) bitwise integer aggregates named bit_and and bit_or for > int2, int4, int8 and bit types. They are not standard, but I find > them useful. I needed them once. > > > The patches adds: > > - 2 new very short strict functions for boolean aggregates in > src/backed/utils/adt/bool.c, > src/include/utils/builtins.h and src/include/catalog/pg_proc.h > > - the new aggregates declared in src/include/catalog/pg_proc.h and > src/include/catalog/pg_aggregate.h > > - some documentation and validation about these new aggregates. > > > It also updates the catalog version. It validates for me. > > Have a nice day, > > -- > Fabien Coelho - coelho@cri.ensmp.fr Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- 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
Fabien COELHO wrote: > (1) boolean-and and boolean-or aggregates named bool_and and bool_or. > they (SHOULD;-) correspond to standard sql every and some/any aggregates. > they do not have the right name as there is a problem with > the standard and the parser for some/any. Tom also think that > the standard name is misleading because NULL are ignored. As I understand it, there's an ambiguity issue with SOME/ANY, but not with EVERY. If so, can we implement EVERY per-spec at least? It's okay if we just add EVERY as an alias for BOOL_AND for the sake of homogeneity. A few trivial points: > + /* EVERY aggregate implementation conforming to SQL 2003 standard. > + * must be strict. > + */ This comment is misleading if we don't actually provide an implementation of EVERY that conforms to spec. There's a similar comment WRT to SOME/ANY. > + PG_FUNCTION_INFO_V1(booland_statefunc); Not needed for builtin functions (they are assumed to be V1). > + /* what about every? */ > + DATA(insert OID = 2517 ( bool_and PGNSP PGUID 12 t f f f i 1 16 "16" _null_ aggregate_dummy -_null_ )); > + DESCR("boolean-and aggregate"); > + /* what about any/some? */ Seems these questions should be removed, no? -Neil
Dear Neil, > As I understand it, there's an ambiguity issue with SOME/ANY, but not > with EVERY. If so, can we implement EVERY per-spec at least? It's okay > if we just add EVERY as an alias for BOOL_AND for the sake of homogeneity. Ok. > > + /* EVERY aggregate implementation conforming to SQL 2003 standard. > > + * must be strict. > > + */ > > This comment is misleading if we don't actually provide an > implementation of EVERY that conforms to spec. There's a similar comment > WRT to SOME/ANY. I agree it is somehow misleading. I'll clarify. > > + PG_FUNCTION_INFO_V1(booland_statefunc); > Not needed for builtin functions (they are assumed to be V1). Ok, I'll drop that. > > + /* what about every? */ > > + DATA(insert OID = 2517 ( bool_and PGNSP PGUID 12 t f f f i 1 16 "16" _null_ aggregate_dummy- _null_ )); > > + DESCR("boolean-and aggregate"); > > + /* what about any/some? */ > > Seems these questions should be removed, no? Well, the question really means "what about naming it every", that is you're very question above! I'll do a fix wrt to your comments, and send a 4th version. Thanks for your comments. -- Fabien Coelho - coelho@cri.ensmp.fr