Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8096fe4

Browse files
committed
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. Also add 'every' aggregate.(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.Fabien COELHO
1 parent3dc37cd commit8096fe4

File tree

8 files changed

+444
-7
lines changed

8 files changed

+444
-7
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 112 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.203 2004/05/19 23:56:38 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.204 2004/05/26 15:25:57 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -7553,6 +7553,76 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
75537553
<entry>the average (arithmetic mean) of all input values</entry>
75547554
</row>
75557555

7556+
<row>
7557+
<entry>
7558+
<indexterm>
7559+
<primary>bit_and</primary>
7560+
</indexterm>
7561+
<function>bit_and(<replaceable class="parameter">expression</replaceable>)</function>
7562+
</entry>
7563+
<entry>
7564+
<type>smallint</type>, <type>integer</type>, <type>bigint</type> or
7565+
<type>bit</type>,
7566+
</entry>
7567+
<entry>
7568+
same as argument data type.
7569+
</entry>
7570+
<entry>the bitwise-and of all non-null input values, or null if empty
7571+
</entry>
7572+
</row>
7573+
7574+
<row>
7575+
<entry>
7576+
<indexterm>
7577+
<primary>bit_or</primary>
7578+
</indexterm>
7579+
<function>bit_or(<replaceable class="parameter">expression</replaceable>)</function>
7580+
</entry>
7581+
<entry>
7582+
<type>smallint</type>, <type>integer</type>, <type>bigint</type> or
7583+
<type>bit</type>,
7584+
</entry>
7585+
<entry>
7586+
same as argument data type.
7587+
</entry>
7588+
<entry>the bitwise-or of all non-null input values, or null if empty.
7589+
</entry>
7590+
</row>
7591+
7592+
<row>
7593+
<entry>
7594+
<indexterm>
7595+
<primary>bool_and</primary>
7596+
</indexterm>
7597+
<function>bool_and(<replaceable class="parameter">expression</replaceable>)</function>
7598+
</entry>
7599+
<entry>
7600+
<type>bool</type>
7601+
</entry>
7602+
<entry>
7603+
<type>bool</type>
7604+
</entry>
7605+
<entry>true if all input values are true, otherwise false.
7606+
Also known as <function>bool_and</function>.
7607+
</entry>
7608+
</row>
7609+
7610+
<row>
7611+
<entry>
7612+
<indexterm>
7613+
<primary>bool_or</primary>
7614+
</indexterm>
7615+
<function>bool_or(<replaceable class="parameter">expression</replaceable>)</function>
7616+
</entry>
7617+
<entry>
7618+
<type>bool</type>
7619+
</entry>
7620+
<entry>
7621+
<type>bool</type>
7622+
</entry>
7623+
<entry>true if at least one input value is true, otherwise false</entry>
7624+
</row>
7625+
75567626
<row>
75577627
<entry><function>count(*)</function></entry>
75587628
<entry></entry>
@@ -7570,6 +7640,24 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
75707640
</entry>
75717641
</row>
75727642

7643+
<row>
7644+
<entry>
7645+
<indexterm>
7646+
<primary>every</primary>
7647+
</indexterm>
7648+
<function>every(<replaceable class="parameter">expression</replaceable>)</function>
7649+
</entry>
7650+
<entry>
7651+
<type>bool</type>
7652+
</entry>
7653+
<entry>
7654+
<type>bool</type>
7655+
</entry>
7656+
<entry>true if all input values are true, otherwise false.
7657+
Also known as <function>bool_and</function>.
7658+
</entry>
7659+
</row>
7660+
75737661
<row>
75747662
<entry><function>max(<replaceable class="parameter">expression</replaceable>)</function></entry>
75757663
<entry>any numeric, string, or date/time type</entry>
@@ -7660,6 +7748,29 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
76607748
used to substitute zero for null when necessary.
76617749
</para>
76627750

7751+
<note>
7752+
<indexterm>
7753+
<primary>ANY</primary>
7754+
</indexterm>
7755+
<indexterm>
7756+
<primary>SOME</primary>
7757+
</indexterm>
7758+
<para>
7759+
Boolean aggregates <function>bool_and</function> and
7760+
<function>bool_or</function> correspond to standard SQL aggregates
7761+
<function>every</function> and <function>any</function> or
7762+
<function>some</function>.
7763+
As for <function>any</function> and <function>some</function>,
7764+
it seems that there is an ambiguity built into the standard syntax:
7765+
<programlisting>
7766+
SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
7767+
</programlisting>
7768+
Here <function>ANY</function> can be considered both as leading
7769+
to a subquery or as an aggregate if the select expression returns 1 row.
7770+
Thus the standard name cannot be given to these aggregates.
7771+
</para>
7772+
</note>
7773+
76637774
<note>
76647775
<para>
76657776
Users accustomed to working with other SQL database management

‎src/backend/utils/adt/bool.c

Lines changed: 21 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/bool.c,v 1.32 2004/05/07 00:24:58 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/bool.c,v 1.33 2004/05/26 15:25:59 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -248,3 +248,23 @@ isnotfalse(PG_FUNCTION_ARGS)
248248

249249
PG_RETURN_BOOL(b);
250250
}
251+
252+
/*
253+
* boolean-and and boolean-or aggregates.
254+
*/
255+
256+
/* function for standard EVERY aggregate implementation conforming to SQL 2003.
257+
* must be strict. It is also named bool_and for homogeneity.
258+
*/
259+
Datumbooland_statefunc(PG_FUNCTION_ARGS)
260+
{
261+
PG_RETURN_BOOL(PG_GETARG_BOOL(0)&&PG_GETARG_BOOL(1));
262+
}
263+
264+
/* function for standard ANY/SOME aggregate conforming to SQL 2003.
265+
* must be strict. The name of the aggregate is bool_or. See the doc.
266+
*/
267+
Datumboolor_statefunc(PG_FUNCTION_ARGS)
268+
{
269+
PG_RETURN_BOOL(PG_GETARG_BOOL(0)||PG_GETARG_BOOL(1));
270+
}

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.230 2004/05/14 21:42:28 neilc Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.231 2004/05/26 15:26:00 momjian Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200405141
56+
#defineCATALOG_VERSION_NO200405261
5757

5858
#endif

‎src/include/catalog/pg_aggregate.h

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.42 2003/11/29 22:40:58 pgsql Exp $
11+
* $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.43 2004/05/26 15:26:03 momjian Exp $
1212
*
1313
* NOTES
1414
* the genbki.sh script reads this file and generates .bki
@@ -149,6 +149,21 @@ DATA(insert ( 2157float4_accumfloat8_stddev1022"{0,0,0}" ));
149149
DATA(insert (2158float8_accumfloat8_stddev1022"{0,0,0}" ));
150150
DATA(insert (2159numeric_accumnumeric_stddev1231"{0,0,0}" ));
151151

152+
/* boolean-and and boolean-or */
153+
DATA(insert (2517booland_statefunc-16_null_ ));
154+
DATA(insert (2518boolor_statefunc-16_null_ ));
155+
DATA(insert (2519booland_statefunc-16_null_ ));
156+
157+
/* bitwise integer */
158+
DATA(insert (2535int2and-21_null_ ));
159+
DATA(insert (2536int2or-21_null_ ));
160+
DATA(insert (2537int4and-23_null_ ));
161+
DATA(insert (2538int4or-23_null_ ));
162+
DATA(insert (2539int8and-20_null_ ));
163+
DATA(insert (2540int8or-20_null_ ));
164+
DATA(insert (2541bitand-1560_null_ ));
165+
DATA(insert (2542bitor-1560_null_ ));
166+
152167
/*
153168
* prototypes for functions in pg_aggregate.c
154169
*/

‎src/include/catalog/pg_proc.h

Lines changed: 36 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.329 2004/05/14 21:42:28 neilc Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.330 2004/05/26 15:26:04 momjian Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -3537,6 +3537,41 @@ DATA(insert OID = 1069 ( generate_series PGNSP PGUID 12 f f t t v 2 20 "20 20"
35373537
DESCR("non-persistent series generator");
35383538

35393539

3540+
/* boolean aggregates */
3541+
DATA(insertOID=2515 (booland_statefuncPGNSPPGUID12fftfi216"16 16"_null_booland_statefunc-_null_ ));
3542+
DESCR("boolean-and aggregate transition function");
3543+
DATA(insertOID=2516 (boolor_statefuncPGNSPPGUID12fftfi216"16 16"_null_boolor_statefunc-_null_ ));
3544+
DESCR("boolean-or aggregate transition function");
3545+
3546+
DATA(insertOID=2517 (bool_andPGNSPPGUID12tfffi116"16"_null_aggregate_dummy-_null_ ));
3547+
DESCR("boolean-and aggregate");
3548+
/* ANY, SOME? These names conflict with subquery operators. See doc. */
3549+
DATA(insertOID=2518 (bool_orPGNSPPGUID12tfffi116"16"_null_aggregate_dummy-_null_ ));
3550+
DESCR("boolean-or aggregate");
3551+
DATA(insertOID=2519 (everyPGNSPPGUID12tfffi116"16"_null_aggregate_dummy-_null_ ));
3552+
DESCR("boolean-and aggregate");
3553+
3554+
/* bitwise integer aggregates */
3555+
DATA(insertOID=2535 (bit_andPGNSPPGUID12tfffi121"21"_null_aggregate_dummy-_null_));
3556+
DESCR("bitwise-and smallint aggregate");
3557+
DATA(insertOID=2536 (bit_orPGNSPPGUID12tfffi121"21"_null_aggregate_dummy-_null_));
3558+
DESCR("bitwise-or smallint aggregate");
3559+
3560+
DATA(insertOID=2537 (bit_andPGNSPPGUID12tfffi123"23"_null_aggregate_dummy-_null_));
3561+
DESCR("bitwise-and integer aggregate");
3562+
DATA(insertOID=2538 (bit_orPGNSPPGUID12tfffi123"23"_null_aggregate_dummy-_null_));
3563+
DESCR("bitwise-or integer aggregate");
3564+
3565+
DATA(insertOID=2539 (bit_andPGNSPPGUID12tfffi120"20"_null_aggregate_dummy-_null_));
3566+
DESCR("bitwise-and bigint aggregate");
3567+
DATA(insertOID=2540 (bit_orPGNSPPGUID12tfffi120"20"_null_aggregate_dummy-_null_));
3568+
DESCR("bitwise-or bigint aggregate");
3569+
3570+
DATA(insertOID=2541 (bit_andPGNSPPGUID12tfffi11560"1560"_null_aggregate_dummy-_null_));
3571+
DESCR("bitwise-and bit aggregate");
3572+
DATA(insertOID=2542 (bit_orPGNSPPGUID12tfffi11560"1560"_null_aggregate_dummy-_null_));
3573+
DESCR("bitwise-or bit aggregate");
3574+
35403575
/*
35413576
* Symbolic values for provolatile column: these indicate whether the result
35423577
* of a function is dependent *only* on the values of its explicit arguments,

‎src/include/utils/builtins.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.238 2004/05/14 21:42:30 neilc Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.239 2004/05/26 15:26:18 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -68,6 +68,8 @@ extern Datum istrue(PG_FUNCTION_ARGS);
6868
externDatumisfalse(PG_FUNCTION_ARGS);
6969
externDatumisnottrue(PG_FUNCTION_ARGS);
7070
externDatumisnotfalse(PG_FUNCTION_ARGS);
71+
externDatumbooland_statefunc(PG_FUNCTION_ARGS);
72+
externDatumboolor_statefunc(PG_FUNCTION_ARGS);
7173

7274
/* char.c */
7375
externDatumcharin(PG_FUNCTION_ARGS);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp