Dear Bruce,
> Agreed, these seem to be of general interest and have been requested in
> the past. I will clean up the docs a little.
Please find attached a new version to address Neil's comments.
- add "every" anyway, next to "bool_and".
- cleaner source and comments.
I also added more regression tests, including the added "EVERY" aggregate.
It DOES NOT validate for me, as errors and rules are broken in current
head:
undefined symbol: pg_strcasecmp
However the aggregate part works fine.
Have a nice day,
--
Fabien Coelho - [EMAIL PROTECTED]*** ./doc/src/sgml/func.sgml.orig Mon May 17 14:00:06 2004
--- ./doc/src/sgml/func.sgml Wed May 19 10:27:17 2004
***************
*** 7554,7559 ****
--- 7554,7629 ----
</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.
+ Also known as <function>bool_and</function>.
+ </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>
***************
*** 7571,7576 ****
--- 7641,7664 ----
</row>
<row>
+ <entry>
+ <indexterm>
+ <primary>every</primary>
+ </indexterm>
+ <function>every(<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.
+ Also known as <function>bool_and</function>.
+ </entry>
+ </row>
+
+ <row>
<entry><function>max(<replaceable
class="parameter">expression</replaceable>)</function></entry>
<entry>any numeric, string, or date/time type</entry>
<entry>same as argument type</entry>
***************
*** 7661,7666 ****
--- 7749,7777 ----
</para>
<note>
+ <indexterm>
+ <primary>ANY</primary>
+ </indexterm>
+ <indexterm>
+ <primary>SOME</primary>
+ </indexterm>
+ <para>
+ Boolean aggregates <function>bool_and</function> and
+ <function>bool_or</function> correspond to standard SQL aggregates
+ <function>every</function> and <function>any</function> or
+ <function>some</function>.
+ As for <function>any</function> and <function>some</function>,
+ it seems that there is an ambiguity built into the standard syntax:
+ <programlisting>
+ SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
+ </programlisting>
+ Here <function>ANY</function> can be considered both as leading
+ to a subquery or as an aggregate if the select expression returns 1 row.
+ Thus the standard name cannot be given to these aggregates.
+ </para>
+ </note>
+
+ <note>
<para>
Users accustomed to working with other SQL database management
systems may be surprised by the performance characteristics of
*** ./src/backend/utils/adt/bool.c.orig Mon May 17 14:00:09 2004
--- ./src/backend/utils/adt/bool.c Wed May 19 10:18:13 2004
***************
*** 248,250 ****
--- 248,270 ----
PG_RETURN_BOOL(b);
}
+
+ /*
+ * boolean-and and boolean-or aggregates.
+ */
+
+ /* function for standard EVERY aggregate implementation conforming to SQL 2003.
+ * must be strict. It is also named bool_and for homogeneity.
+ */
+ Datum booland_statefunc(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_BOOL(PG_GETARG_BOOL(0) && PG_GETARG_BOOL(1));
+ }
+
+ /* function for standard ANY/SOME aggregate conforming to SQL 2003.
+ * must be strict. The name of the aggregate is bool_or. See the doc.
+ */
+ Datum boolor_statefunc(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_BOOL(PG_GETARG_BOOL(0) || PG_GETARG_BOOL(1));
+ }
*** ./src/include/catalog/catversion.h.orig Mon May 17 14:00:11 2004
--- ./src/include/catalog/catversion.h Wed May 19 10:22:14 2004
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200405141
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200405191
#endif
*** ./src/include/catalog/pg_aggregate.h.orig Sat Nov 29 23:40:58 2003
--- ./src/include/catalog/pg_aggregate.h Wed May 19 10:19:28 2004
***************
*** 149,154 ****
--- 149,169 ----
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_ ));
+ DATA(insert ( 2519 booland_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 May 17 14:00:12 2004
--- ./src/include/catalog/pg_proc.h Wed May 19 10:21:48 2004
***************
*** 3537,3542 ****
--- 3537,3577 ----
DESCR("non-persistent series generator");
+ /* 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");
+
+ 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");
+ /* ANY, SOME? These names conflict with subquery operators. See doc. */
+ 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");
+ DATA(insert OID = 2519 ( every
PGNSP PGUID 12 t f f f i 1 16 "16" _null_ aggregate_dummy - _null_ ));
+ DESCR("boolean-and 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
* of a function is dependent *only* on the values of its explicit arguments,
*** ./src/include/utils/builtins.h.orig Mon May 17 14:00:12 2004
--- ./src/include/utils/builtins.h Wed May 19 10:15:58 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 Wed May 19 11:32:46 2004
***************
*** 157,159 ****
--- 157,295 ----
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 AS "t",
+ booland_statefunc(TRUE, NULL) IS NULL AS "t",
+ booland_statefunc(FALSE, NULL) IS NULL AS "t",
+ booland_statefunc(NULL, TRUE) IS NULL AS "t",
+ booland_statefunc(NULL, FALSE) IS NULL AS "t",
+ -- and actual computations
+ booland_statefunc(TRUE, TRUE) AS "t",
+ NOT booland_statefunc(TRUE, FALSE) AS "t",
+ NOT booland_statefunc(FALSE, TRUE) AS "t",
+ NOT booland_statefunc(FALSE, FALSE) AS "t";
+ t | t | t | t | t | t | t | t | t
+ ---+---+---+---+---+---+---+---+---
+ t | t | t | t | t | t | t | t | t
+ (1 row)
+
+ SELECT
+ -- boolean or transitions
+ -- null because strict
+ boolor_statefunc(NULL, NULL) IS NULL AS "t",
+ boolor_statefunc(TRUE, NULL) IS NULL AS "t",
+ boolor_statefunc(FALSE, NULL) IS NULL AS "t",
+ boolor_statefunc(NULL, TRUE) IS NULL AS "t",
+ boolor_statefunc(NULL, FALSE) IS NULL AS "t",
+ -- actual computations
+ boolor_statefunc(TRUE, TRUE) AS "t",
+ boolor_statefunc(TRUE, FALSE) AS "t",
+ boolor_statefunc(FALSE, TRUE) AS "t",
+ NOT boolor_statefunc(FALSE, FALSE) AS "t";
+ t | t | t | t | t | t | t | t | t
+ ---+---+---+---+---+---+---+---+---
+ t | t | t | t | t | t | t | 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"
+ FROM bool_test;
+ f | t | f | n | f | t
+ ---+---+---+---+---+---
+ f | t | f | | f | t
+ (1 row)
+
+ SELECT
+ EVERY(b1) AS "f",
+ EVERY(b2) AS "t",
+ EVERY(b3) AS "f",
+ EVERY(b4) AS "n",
+ EVERY(NOT b2) AS "f",
+ EVERY(NOT b3) AS "t"
+ FROM bool_test;
+ f | t | f | n | f | t
+ ---+---+---+---+---+---
+ f | t | f | | f | t
+ (1 row)
+
+ SELECT
+ 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;
+ t | t | f | n | 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 Wed May 19 11:30:31 2004
***************
*** 62,64 ****
--- 62,182 ----
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 AS "t",
+ booland_statefunc(TRUE, NULL) IS NULL AS "t",
+ booland_statefunc(FALSE, NULL) IS NULL AS "t",
+ booland_statefunc(NULL, TRUE) IS NULL AS "t",
+ booland_statefunc(NULL, FALSE) IS NULL AS "t",
+ -- and actual computations
+ booland_statefunc(TRUE, TRUE) AS "t",
+ NOT booland_statefunc(TRUE, FALSE) AS "t",
+ NOT booland_statefunc(FALSE, TRUE) AS "t",
+ NOT booland_statefunc(FALSE, FALSE) AS "t";
+
+ SELECT
+ -- boolean or transitions
+ -- null because strict
+ boolor_statefunc(NULL, NULL) IS NULL AS "t",
+ boolor_statefunc(TRUE, NULL) IS NULL AS "t",
+ boolor_statefunc(FALSE, NULL) IS NULL AS "t",
+ boolor_statefunc(NULL, TRUE) IS NULL AS "t",
+ boolor_statefunc(NULL, FALSE) IS NULL AS "t",
+ -- actual computations
+ boolor_statefunc(TRUE, TRUE) AS "t",
+ boolor_statefunc(TRUE, FALSE) AS "t",
+ boolor_statefunc(FALSE, TRUE) AS "t",
+ NOT boolor_statefunc(FALSE, FALSE) AS "t";
+
+ 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"
+ FROM bool_test;
+
+ SELECT
+ EVERY(b1) AS "f",
+ EVERY(b2) AS "t",
+ EVERY(b3) AS "f",
+ EVERY(b4) AS "n",
+ EVERY(NOT b2) AS "f",
+ EVERY(NOT b3) AS "t"
+ FROM bool_test;
+
+ SELECT
+ 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 8: explain analyze is your friend
