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

Commitd95425c

Browse files
committed
Provide moving-aggregate support for boolean aggregates.
David Rowley and Florian Pflug, reviewed by Dean Rasheed
1 parent842faa7 commitd95425c

File tree

7 files changed

+137
-8
lines changed

7 files changed

+137
-8
lines changed

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

Lines changed: 105 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -283,20 +283,121 @@ boolge(PG_FUNCTION_ARGS)
283283
* boolean-and and boolean-or aggregates.
284284
*/
285285

286-
/* function for standard EVERY aggregate implementation conforming to SQL 2003.
287-
* must be strict. It is also named bool_and for homogeneity.
286+
/*
287+
* Function for standard EVERY aggregate conforming to SQL 2003.
288+
* The aggregate is also named bool_and for consistency.
289+
*
290+
* Note: this is only used in plain aggregate mode, not moving-aggregate mode.
288291
*/
289292
Datum
290293
booland_statefunc(PG_FUNCTION_ARGS)
291294
{
292295
PG_RETURN_BOOL(PG_GETARG_BOOL(0)&&PG_GETARG_BOOL(1));
293296
}
294297

295-
/* function for standard ANY/SOME aggregate conforming to SQL 2003.
296-
* must be strict. The name of the aggregate is bool_or. See the doc.
298+
/*
299+
* Function for standard ANY/SOME aggregate conforming to SQL 2003.
300+
* The aggregate is named bool_or, because ANY/SOME have parsing conflicts.
301+
*
302+
* Note: this is only used in plain aggregate mode, not moving-aggregate mode.
297303
*/
298304
Datum
299305
boolor_statefunc(PG_FUNCTION_ARGS)
300306
{
301307
PG_RETURN_BOOL(PG_GETARG_BOOL(0)||PG_GETARG_BOOL(1));
302308
}
309+
310+
typedefstructBoolAggState
311+
{
312+
int64aggcount;/* number of non-null values aggregated */
313+
int64aggtrue;/* number of values aggregated that are true */
314+
}BoolAggState;
315+
316+
staticBoolAggState*
317+
makeBoolAggState(FunctionCallInfofcinfo)
318+
{
319+
BoolAggState*state;
320+
MemoryContextagg_context;
321+
322+
if (!AggCheckCallContext(fcinfo,&agg_context))
323+
elog(ERROR,"aggregate function called in non-aggregate context");
324+
325+
state= (BoolAggState*)MemoryContextAlloc(agg_context,
326+
sizeof(BoolAggState));
327+
state->aggcount=0;
328+
state->aggtrue=0;
329+
330+
returnstate;
331+
}
332+
333+
Datum
334+
bool_accum(PG_FUNCTION_ARGS)
335+
{
336+
BoolAggState*state;
337+
338+
state=PG_ARGISNULL(0) ?NULL : (BoolAggState*)PG_GETARG_POINTER(0);
339+
340+
/* Create the state data on first call */
341+
if (state==NULL)
342+
state=makeBoolAggState(fcinfo);
343+
344+
if (!PG_ARGISNULL(1))
345+
{
346+
state->aggcount++;
347+
if (PG_GETARG_BOOL(1))
348+
state->aggtrue++;
349+
}
350+
351+
PG_RETURN_POINTER(state);
352+
}
353+
354+
Datum
355+
bool_accum_inv(PG_FUNCTION_ARGS)
356+
{
357+
BoolAggState*state;
358+
359+
state=PG_ARGISNULL(0) ?NULL : (BoolAggState*)PG_GETARG_POINTER(0);
360+
361+
/* bool_accum should have created the state data */
362+
if (state==NULL)
363+
elog(ERROR,"bool_accum_inv called with NULL state");
364+
365+
if (!PG_ARGISNULL(1))
366+
{
367+
state->aggcount--;
368+
if (PG_GETARG_BOOL(1))
369+
state->aggtrue--;
370+
}
371+
372+
PG_RETURN_POINTER(state);
373+
}
374+
375+
Datum
376+
bool_alltrue(PG_FUNCTION_ARGS)
377+
{
378+
BoolAggState*state;
379+
380+
state=PG_ARGISNULL(0) ?NULL : (BoolAggState*)PG_GETARG_POINTER(0);
381+
382+
/* if there were no non-null values, return NULL */
383+
if (state==NULL||state->aggcount==0)
384+
PG_RETURN_NULL();
385+
386+
/* true if all non-null values are true */
387+
PG_RETURN_BOOL(state->aggtrue==state->aggcount);
388+
}
389+
390+
Datum
391+
bool_anytrue(PG_FUNCTION_ARGS)
392+
{
393+
BoolAggState*state;
394+
395+
state=PG_ARGISNULL(0) ?NULL : (BoolAggState*)PG_GETARG_POINTER(0);
396+
397+
/* if there were no non-null values, return NULL */
398+
if (state==NULL||state->aggcount==0)
399+
PG_RETURN_NULL();
400+
401+
/* true if any non-null value is true */
402+
PG_RETURN_BOOL(state->aggtrue>0);
403+
}

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201404122
56+
#defineCATALOG_VERSION_NO201404123
5757

5858
#endif

‎src/include/catalog/pg_aggregate.h

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -248,9 +248,9 @@ DATA(insert ( 2828n 0 float8_regr_accumfloat8_covar_samp---0102
248248
DATA(insert (2829n0float8_regr_accumfloat8_corr---01022000"{0,0,0,0,0,0}"_null_ ));
249249

250250
/* boolean-and and boolean-or */
251-
DATA(insert (2517n0booland_statefunc----5816000_null__null_ ));
252-
DATA(insert (2518n0boolor_statefunc----5916000_null__null_ ));
253-
DATA(insert (2519n0booland_statefunc----5816000_null__null_ ));
251+
DATA(insert (2517n0booland_statefunc-bool_accumbool_accum_invbool_alltrue58160228116_null__null_ ));
252+
DATA(insert (2518n0boolor_statefunc-bool_accumbool_accum_invbool_anytrue59160228116_null__null_ ));
253+
DATA(insert (2519n0booland_statefunc-bool_accumbool_accum_invbool_alltrue58160228116_null__null_ ));
254254

255255
/* bitwise integer */
256256
DATA(insert (2236n0int2and----021000_null__null_ ));

‎src/include/catalog/pg_proc.h

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3915,6 +3915,14 @@ DATA(insert OID = 2515 ( booland_statefunc PGNSP PGUID 12 1 0 0 0 f f f f t
39153915
DESCR("aggregate transition function");
39163916
DATA(insertOID=2516 (boolor_statefuncPGNSPPGUID121000fffftfi2016"16 16"_null__null__null__null_boolor_statefunc_null__null__null_ ));
39173917
DESCR("aggregate transition function");
3918+
DATA(insertOID=3496 (bool_accumPGNSPPGUID121000ffffffi202281"2281 16"_null__null__null__null_bool_accum_null__null__null_ ));
3919+
DESCR("aggregate transition function");
3920+
DATA(insertOID=3497 (bool_accum_invPGNSPPGUID121000ffffffi202281"2281 16"_null__null__null__null_bool_accum_inv_null__null__null_ ));
3921+
DESCR("aggregate transition function");
3922+
DATA(insertOID=3498 (bool_alltruePGNSPPGUID121000fffftfi1016"2281"_null__null__null__null_bool_alltrue_null__null__null_ ));
3923+
DESCR("aggregate final function");
3924+
DATA(insertOID=3499 (bool_anytruePGNSPPGUID121000fffftfi1016"2281"_null__null__null__null_bool_anytrue_null__null__null_ ));
3925+
DESCR("aggregate final function");
39183926
DATA(insertOID=2517 (bool_andPGNSPPGUID121000tfffffi1016"16"_null__null__null__null_aggregate_dummy_null__null__null_ ));
39193927
DESCR("boolean-and aggregate");
39203928
/* ANY, SOME? These names conflict with subquery operators. See doc. */

‎src/include/utils/builtins.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -121,6 +121,10 @@ extern Datum boolle(PG_FUNCTION_ARGS);
121121
externDatumboolge(PG_FUNCTION_ARGS);
122122
externDatumbooland_statefunc(PG_FUNCTION_ARGS);
123123
externDatumboolor_statefunc(PG_FUNCTION_ARGS);
124+
externDatumbool_accum(PG_FUNCTION_ARGS);
125+
externDatumbool_accum_inv(PG_FUNCTION_ARGS);
126+
externDatumbool_alltrue(PG_FUNCTION_ARGS);
127+
externDatumbool_anytrue(PG_FUNCTION_ARGS);
124128
externboolparse_bool(constchar*value,bool*result);
125129
externboolparse_bool_with_len(constchar*value,size_tlen,bool*result);
126130

‎src/test/regress/expected/window.out

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1769,3 +1769,15 @@ SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FO
17691769
1.0
17701770
(2 rows)
17711771

1772+
SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
1773+
FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b)
1774+
WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING);
1775+
i | b | bool_and | bool_or
1776+
---+---+----------+---------
1777+
1 | t | t | t
1778+
2 | t | f | t
1779+
3 | f | f | f
1780+
4 | f | f | t
1781+
5 | t | t | t
1782+
(5 rows)
1783+

‎src/test/regress/sql/window.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -617,3 +617,7 @@ FROM (VALUES(1,1::numeric),(2,2),(3,'NaN'),(4,3),(5,4)) t(a,b);
617617
-- hard about it.
618618
SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROWAND1 FOLLOWING),'999999999999999999999D9')
619619
FROM (VALUES(1,1e20),(2,1)) n(i,n);
620+
621+
SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
622+
FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b)
623+
WINDOW wAS (ORDER BY i ROWS BETWEEN CURRENT ROWAND1 FOLLOWING);

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp