Dear patchers,
please find attached my second patch submission for adding
new aggregate functions:
(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, and I'm not the first one to look for
them.
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 - [EMAIL PROTECTED]*** ./doc/src/sgml/func.sgml.orig Mon Apr 26 14:00:58 2004
--- ./doc/src/sgml/func.sgml Sun May 2 12:04:07 2004
***************
*** 7544,7549 ****
--- 7544,7617 ----
</row>
<row>
+ <entry>
+ <indexterm>
+ <primary>bit_and</primary>
+ </indexterm>
+ <function>bit_and(<replaceable
class="parameter">expression</replaceable>)</function>
+ </entry>
+ <entry>
+ <type>smallint</type>, <type>integer</type>, <type>bigint</type> or
+ <type>bit</type>,
+ </entry>
+ <entry>
+ same as argument data type.
+ </entry>
+ <entry>the bitwise-and of all non-null input values, or null if empty
+ </entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>bit_or</primary>
+ </indexterm>
+ <function>bit_or(<replaceable
class="parameter">expression</replaceable>)</function>
+ </entry>
+ <entry>
+ <type>smallint</type>, <type>integer</type>, <type>bigint</type> or
+ <type>bit</type>,
+ </entry>
+ <entry>
+ same as argument data type.
+ </entry>
+ <entry>the bitwise-or of all non-null input values, or null if empty.
+ </entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>bool_and</primary>
+ </indexterm>
+ <function>bool_and(<replaceable
class="parameter">expression</replaceable>)</function>
+ </entry>
+ <entry>
+ <type>bool</type>
+ </entry>
+ <entry>
+ <type>bool</type>
+ </entry>
+ <entry>true if all input values are true, otherwise false</entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>bool_or</primary>
+ </indexterm>
+ <function>bool_or(<replaceable
class="parameter">expression</replaceable>)</function>
+ </entry>
+ <entry>
+ <type>bool</type>
+ </entry>
+ <entry>
+ <type>bool</type>
+ </entry>
+ <entry>true if at least one input value is true, otherwise false</entry>
+ </row>
+
+ <row>
<entry><function>count(*)</function></entry>
<entry></entry>
<entry><type>bigint</type></entry>
*** ./src/backend/utils/adt/bool.c.orig Sat Nov 29 20:51:58 2003
--- ./src/backend/utils/adt/bool.c Sun May 2 11:32:03 2004
***************
*** 248,250 ****
--- 248,279 ----
PG_RETURN_BOOL(b);
}
+
+ /* boolean-and and boolean-or aggregates.
+ *
+ * this correspond to EVERY and ANY/SOME aggregate in SQL-2003.
+ * There is an ambiguity in the syntax shown by Tom:
+ * SELECT b1 = ANY((SELECT b2 FROM t2)) FROM t1;
+ *
+ * can be interpreted as either an aggregate or as an any sub-select.
+ */
+
+ /* EVERY aggregate implementation conforming to SQL 2003 standard.
+ * must be strict.
+ */
+ PG_FUNCTION_INFO_V1(booland_statefunc);
+
+ Datum booland_statefunc(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_BOOL(PG_GETARG_BOOL(0) && PG_GETARG_BOOL(1));
+ }
+
+ /* SOME aggregate implementation conforming to SQL 2003 standard.
+ * must be strict.
+ */
+ PG_FUNCTION_INFO_V1(boolor_statefunc);
+
+ Datum boolor_statefunc(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_BOOL(PG_GETARG_BOOL(0) || PG_GETARG_BOOL(1));
+ }
*** ./src/include/catalog/catversion.h.orig Mon Apr 26 18:50:08 2004
--- ./src/include/catalog/catversion.h Sun May 2 11:15:37 2004
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200404260
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200405010
#endif
*** ./src/include/catalog/pg_aggregate.h.orig Sat Nov 29 23:40:58 2003
--- ./src/include/catalog/pg_aggregate.h Sun May 2 11:34:05 2004
***************
*** 149,154 ****
--- 149,168 ----
DATA(insert ( 2158 float8_accum float8_stddev 1022 "{0,0,0}" ));
DATA(insert ( 2159 numeric_accum numeric_stddev 1231 "{0,0,0}" ));
+ /* boolean-and and boolean-or */
+ DATA(insert ( 2517 booland_statefunc - 16
_null_ ));
+ DATA(insert ( 2518 boolor_statefunc - 16
_null_ ));
+
+ /* bitwise integer */
+ DATA(insert ( 2535 int2and - 21 _null_ ));
+ DATA(insert ( 2536 int2or - 21 _null_ ));
+ DATA(insert ( 2537 int4and - 23 _null_ ));
+ DATA(insert ( 2538 int4or - 23 _null_ ));
+ DATA(insert ( 2539 int8and - 20 _null_ ));
+ DATA(insert ( 2540 int8or - 20 _null_ ));
+ DATA(insert ( 2541 bitand - 1560 _null_ ));
+ DATA(insert ( 2542 bitor - 1560 _null_ ));
+
/*
* prototypes for functions in pg_aggregate.c
*/
*** ./src/include/catalog/pg_proc.h.orig Mon Apr 26 18:50:10 2004
--- ./src/include/catalog/pg_proc.h Sun May 2 11:33:02 2004
***************
*** 3546,3551 ****
--- 3546,3584 ----
DATA(insert OID = 2514 ( aclitem_goptions PGNSP PGUID 12 f f
t f i 1 23 "1033" _null_ aclitem_goptions - _null_ ));
DESCR("extract grant options from aclitem");
+ /* boolean aggregates */
+ DATA(insert OID = 2515 ( booland_statefunc PGNSP PGUID 12 f f t f i 2 16
"16 16" _null_ booland_statefunc - _null_ ));
+ DESCR("boolean-and aggregate transition function");
+ DATA(insert OID = 2516 ( boolor_statefunc PGNSP PGUID 12 f f t f i 2 16
"16 16" _null_ boolor_statefunc - _null_ ));
+ DESCR("boolean-or aggregate transition function");
+
+ /* 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? */
+ DATA(insert OID = 2518 ( bool_or PGNSP PGUID
12 t f f f i 1 16 "16" _null_ aggregate_dummy - _null_ ));
+ DESCR("boolean-or aggregate");
+
+ /* bitwise integer aggregates */
+ DATA(insert OID = 2535 ( bit_and PGNSP PGUID
12 t f f f i 1 21 "21" _null_ aggregate_dummy - _null_));
+ DESCR("bitwise-and smallint aggregate");
+ DATA(insert OID = 2536 ( bit_or
PGNSP PGUID 12 t f f f i 1 21 "21" _null_ aggregate_dummy - _null_));
+ DESCR("bitwise-or smallint aggregate");
+
+ DATA(insert OID = 2537 ( bit_and PGNSP PGUID
12 t f f f i 1 23 "23" _null_ aggregate_dummy - _null_));
+ DESCR("bitwise-and integer aggregate");
+ DATA(insert OID = 2538 ( bit_or
PGNSP PGUID 12 t f f f i 1 23 "23" _null_ aggregate_dummy - _null_));
+ DESCR("bitwise-or integer aggregate");
+
+ DATA(insert OID = 2539 ( bit_and PGNSP PGUID
12 t f f f i 1 20 "20" _null_ aggregate_dummy - _null_));
+ DESCR("bitwise-and bigint aggregate");
+ DATA(insert OID = 2540 ( bit_or
PGNSP PGUID 12 t f f f i 1 20 "20" _null_ aggregate_dummy - _null_));
+ DESCR("bitwise-or bigint aggregate");
+
+ DATA(insert OID = 2541 ( bit_and PGNSP PGUID
12 t f f f i 1 1560 "1560" _null_ aggregate_dummy - _null_));
+ DESCR("bitwise-and bit aggregate");
+ DATA(insert OID = 2542 ( bit_or
PGNSP PGUID 12 t f f f i 1 1560 "1560" _null_ aggregate_dummy - _null_));
+ DESCR("bitwise-or bit aggregate");
/*
* Symbolic values for provolatile column: these indicate whether the result
*** ./src/include/utils/builtins.h.orig Mon Apr 5 12:06:43 2004
--- ./src/include/utils/builtins.h Sun May 2 11:28:20 2004
***************
*** 68,73 ****
--- 68,75 ----
extern Datum isfalse(PG_FUNCTION_ARGS);
extern Datum isnottrue(PG_FUNCTION_ARGS);
extern Datum isnotfalse(PG_FUNCTION_ARGS);
+ extern Datum booland_statefunc(PG_FUNCTION_ARGS);
+ extern Datum boolor_statefunc(PG_FUNCTION_ARGS);
/* char.c */
extern Datum charin(PG_FUNCTION_ARGS);
*** ./src/test/regress/expected/aggregates.out.orig Sat Jul 19 22:20:52 2003
--- ./src/test/regress/expected/aggregates.out Sun May 2 12:00:14 2004
***************
*** 157,159 ****
--- 157,270 ----
having exists (select 1 from onek b
where sum(distinct a.four + b.four) = b.four);
ERROR: aggregates not allowed in WHERE clause
+ --
+ -- test for bitwise integer aggregates
+ --
+ CREATE TEMPORARY TABLE bitwise_test(
+ i2 INT2,
+ i4 INT4,
+ i8 INT8,
+ i INTEGER,
+ x INT2,
+ y BIT(4)
+ );
+ -- empty case
+ SELECT
+ BIT_AND(i2) AS "?",
+ BIT_OR(i4) AS "?"
+ FROM bitwise_test;
+ ? | ?
+ ---+---
+ |
+ (1 row)
+
+ COPY bitwise_test FROM STDIN NULL 'null';
+ SELECT
+ BIT_AND(i2) AS "1",
+ BIT_AND(i4) AS "1",
+ BIT_AND(i8) AS "1",
+ BIT_AND(i) AS "?",
+ BIT_AND(x) AS "0",
+ BIT_AND(y) AS "0100",
+ BIT_OR(i2) AS "7",
+ BIT_OR(i4) AS "7",
+ BIT_OR(i8) AS "7",
+ BIT_OR(i) AS "?",
+ BIT_OR(x) AS "7",
+ BIT_OR(y) AS "1101"
+ FROM bitwise_test;
+ 1 | 1 | 1 | ? | 0 | 0100 | 7 | 7 | 7 | ? | 7 | 1101
+ ---+---+---+---+---+------+---+---+---+---+---+------
+ 1 | 1 | 1 | 1 | 0 | 0100 | 7 | 7 | 7 | 3 | 7 | 1101
+ (1 row)
+
+ --
+ -- test boolean aggregates
+ --
+ -- first test all possible transition and final states
+ SELECT
+ -- boolean and transitions
+ -- null because strict
+ booland_statefunc(NULL, NULL) IS NULL
+ AND booland_statefunc(TRUE, NULL) IS NULL
+ AND booland_statefunc(FALSE, NULL) IS NULL
+ AND booland_statefunc(NULL, TRUE) IS NULL
+ AND booland_statefunc(NULL, FALSE) IS NULL
+ -- and actual computations
+ AND booland_statefunc(TRUE, TRUE)
+ AND NOT booland_statefunc(TRUE, FALSE)
+ AND NOT booland_statefunc(FALSE, TRUE)
+ AND NOT booland_statefunc(FALSE, FALSE) AS "booland",
+ -- boolean or transitions
+ -- null because strict
+ boolor_statefunc(NULL, NULL) IS NULL
+ AND boolor_statefunc(TRUE, NULL) IS NULL
+ AND boolor_statefunc(FALSE, NULL) IS NULL
+ AND boolor_statefunc(NULL, TRUE) IS NULL
+ AND boolor_statefunc(NULL, FALSE) IS NULL
+ -- actual computations
+ AND boolor_statefunc(TRUE, TRUE)
+ AND boolor_statefunc(TRUE, FALSE)
+ AND boolor_statefunc(FALSE, TRUE)
+ AND NOT boolor_statefunc(FALSE, FALSE) AS "boolor"
+ ;
+ booland | boolor
+ ---------+--------
+ t | t
+ (1 row)
+
+ CREATE TEMPORARY TABLE bool_test(
+ b1 BOOL,
+ b2 BOOL,
+ b3 BOOL,
+ b4 BOOL);
+ -- empty case
+ SELECT
+ BOOL_AND(b1) AS "n",
+ BOOL_OR(b3) AS "n"
+ FROM bool_test;
+ n | n
+ ---+---
+ |
+ (1 row)
+
+ COPY bool_test FROM STDIN NULL 'null';
+ SELECT
+ BOOL_AND(b1) AS "f",
+ BOOL_AND(b2) AS "t",
+ BOOL_AND(b3) AS "f",
+ BOOL_AND(b4) AS "n",
+ BOOL_AND(NOT b2) AS "f",
+ BOOL_AND(NOT b3) AS "t",
+ BOOL_OR(b1) AS "t",
+ BOOL_OR(b2) AS "t",
+ BOOL_OR(b3) AS "f",
+ BOOL_OR(b4) AS "n",
+ BOOL_OR(NOT b2) AS "f",
+ BOOL_OR(NOT b3) AS "t"
+ FROM bool_test;
+ f | t | f | n | f | t | t | t | f | n | f | t
+ ---+---+---+---+---+---+---+---+---+---+---+---
+ f | t | f | | f | t | t | t | f | | f | t
+ (1 row)
+
*** ./src/test/regress/sql/aggregates.sql.orig Fri Jun 6 17:04:03 2003
--- ./src/test/regress/sql/aggregates.sql Sun May 2 11:56:54 2004
***************
*** 62,64 ****
--- 62,171 ----
group by ten
having exists (select 1 from onek b
where sum(distinct a.four + b.four) = b.four);
+
+ --
+ -- test for bitwise integer aggregates
+ --
+ CREATE TEMPORARY TABLE bitwise_test(
+ i2 INT2,
+ i4 INT4,
+ i8 INT8,
+ i INTEGER,
+ x INT2,
+ y BIT(4)
+ );
+
+ -- empty case
+ SELECT
+ BIT_AND(i2) AS "?",
+ BIT_OR(i4) AS "?"
+ FROM bitwise_test;
+
+ COPY bitwise_test FROM STDIN NULL 'null';
+ 1 1 1 1 1 B0101
+ 3 3 3 null 2 B0100
+ 7 7 7 3 4 B1100
+ \.
+
+ SELECT
+ BIT_AND(i2) AS "1",
+ BIT_AND(i4) AS "1",
+ BIT_AND(i8) AS "1",
+ BIT_AND(i) AS "?",
+ BIT_AND(x) AS "0",
+ BIT_AND(y) AS "0100",
+
+ BIT_OR(i2) AS "7",
+ BIT_OR(i4) AS "7",
+ BIT_OR(i8) AS "7",
+ BIT_OR(i) AS "?",
+ BIT_OR(x) AS "7",
+ BIT_OR(y) AS "1101"
+ FROM bitwise_test;
+
+ --
+ -- test boolean aggregates
+ --
+ -- first test all possible transition and final states
+ SELECT
+ -- boolean and transitions
+ -- null because strict
+ booland_statefunc(NULL, NULL) IS NULL
+ AND booland_statefunc(TRUE, NULL) IS NULL
+ AND booland_statefunc(FALSE, NULL) IS NULL
+ AND booland_statefunc(NULL, TRUE) IS NULL
+ AND booland_statefunc(NULL, FALSE) IS NULL
+
+ -- and actual computations
+ AND booland_statefunc(TRUE, TRUE)
+ AND NOT booland_statefunc(TRUE, FALSE)
+ AND NOT booland_statefunc(FALSE, TRUE)
+ AND NOT booland_statefunc(FALSE, FALSE) AS "booland",
+
+ -- boolean or transitions
+ -- null because strict
+ boolor_statefunc(NULL, NULL) IS NULL
+ AND boolor_statefunc(TRUE, NULL) IS NULL
+ AND boolor_statefunc(FALSE, NULL) IS NULL
+ AND boolor_statefunc(NULL, TRUE) IS NULL
+ AND boolor_statefunc(NULL, FALSE) IS NULL
+ -- actual computations
+ AND boolor_statefunc(TRUE, TRUE)
+ AND boolor_statefunc(TRUE, FALSE)
+ AND boolor_statefunc(FALSE, TRUE)
+ AND NOT boolor_statefunc(FALSE, FALSE) AS "boolor"
+ ;
+
+ CREATE TEMPORARY TABLE bool_test(
+ b1 BOOL,
+ b2 BOOL,
+ b3 BOOL,
+ b4 BOOL);
+
+ -- empty case
+ SELECT
+ BOOL_AND(b1) AS "n",
+ BOOL_OR(b3) AS "n"
+ FROM bool_test;
+
+ COPY bool_test FROM STDIN NULL 'null';
+ TRUE null FALSE null
+ FALSE TRUE null null
+ null TRUE FALSE null
+ \.
+
+ SELECT
+ BOOL_AND(b1) AS "f",
+ BOOL_AND(b2) AS "t",
+ BOOL_AND(b3) AS "f",
+ BOOL_AND(b4) AS "n",
+ BOOL_AND(NOT b2) AS "f",
+ BOOL_AND(NOT b3) AS "t",
+
+ BOOL_OR(b1) AS "t",
+ BOOL_OR(b2) AS "t",
+ BOOL_OR(b3) AS "f",
+ BOOL_OR(b4) AS "n",
+ BOOL_OR(NOT b2) AS "f",
+ BOOL_OR(NOT b3) AS "t"
+ FROM bool_test;
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
