Skip to content

Commit d95425c

Browse files
tglsfdctglsfdc
committed
Provide moving-aggregate support for boolean aggregates.
David Rowley and Florian Pflug, reviewed by Dean Rasheed
1 parent 842faa7 commit d95425c

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+
typedef struct BoolAggState
311+
{
312+
int64 aggcount; /* number of non-null values aggregated */
313+
int64 aggtrue; /* number of values aggregated that are true */
314+
} BoolAggState;
315+
316+
static BoolAggState *
317+
makeBoolAggState(FunctionCallInfo fcinfo)
318+
{
319+
BoolAggState *state;
320+
MemoryContext agg_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+
return state;
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-
#define CATALOG_VERSION_NO 201404122
56+
#define CATALOG_VERSION_NO 201404123
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 ( 2828 n 0 float8_regr_accum float8_covar_samp - - - 0 102
248248
DATA(insert ( 2829 n 0 float8_regr_accum float8_corr - - - 0 1022 0 0 0 "{0,0,0,0,0,0}" _null_ ));
249249

250250
/* boolean-and and boolean-or */
251-
DATA(insert ( 2517 n 0 booland_statefunc - - - - 58 16 0 0 0 _null_ _null_ ));
252-
DATA(insert ( 2518 n 0 boolor_statefunc - - - - 59 16 0 0 0 _null_ _null_ ));
253-
DATA(insert ( 2519 n 0 booland_statefunc - - - - 58 16 0 0 0 _null_ _null_ ));
251+
DATA(insert ( 2517 n 0 booland_statefunc - bool_accum bool_accum_inv bool_alltrue 58 16 0 2281 16 _null_ _null_ ));
252+
DATA(insert ( 2518 n 0 boolor_statefunc - bool_accum bool_accum_inv bool_anytrue 59 16 0 2281 16 _null_ _null_ ));
253+
DATA(insert ( 2519 n 0 booland_statefunc - bool_accum bool_accum_inv bool_alltrue 58 16 0 2281 16 _null_ _null_ ));
254254

255255
/* bitwise integer */
256256
DATA(insert ( 2236 n 0 int2and - - - - 0 21 0 0 0 _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(insert OID = 2516 ( boolor_statefunc PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "16 16" _null_ _null_ _null_ _null_ boolor_statefunc _null_ _null_ _null_ ));
39173917
DESCR("aggregate transition function");
3918+
DATA(insert OID = 3496 ( bool_accum PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 16" _null_ _null_ _null_ _null_ bool_accum _null_ _null_ _null_ ));
3919+
DESCR("aggregate transition function");
3920+
DATA(insert OID = 3497 ( bool_accum_inv PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 16" _null_ _null_ _null_ _null_ bool_accum_inv _null_ _null_ _null_ ));
3921+
DESCR("aggregate transition function");
3922+
DATA(insert OID = 3498 ( bool_alltrue PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 16 "2281" _null_ _null_ _null_ _null_ bool_alltrue _null_ _null_ _null_ ));
3923+
DESCR("aggregate final function");
3924+
DATA(insert OID = 3499 ( bool_anytrue PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 16 "2281" _null_ _null_ _null_ _null_ bool_anytrue _null_ _null_ _null_ ));
3925+
DESCR("aggregate final function");
39183926
DATA(insert OID = 2517 ( bool_and PGNSP PGUID 12 1 0 0 0 t f f f f f i 1 0 16 "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
extern Datum boolge(PG_FUNCTION_ARGS);
122122
extern Datum booland_statefunc(PG_FUNCTION_ARGS);
123123
extern Datum boolor_statefunc(PG_FUNCTION_ARGS);
124+
extern Datum bool_accum(PG_FUNCTION_ARGS);
125+
extern Datum bool_accum_inv(PG_FUNCTION_ARGS);
126+
extern Datum bool_alltrue(PG_FUNCTION_ARGS);
127+
extern Datum bool_anytrue(PG_FUNCTION_ARGS);
124128
extern bool parse_bool(const char *value, bool *result);
125129
extern bool parse_bool_with_len(const char *value, size_t len, 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 ROW AND 1 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 w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING);

0 commit comments

Comments
 (0)