Synopsis

Use the CREATE AGGREGATE statement to create an aggregate function. There are three ways to create aggregates.

Syntax

create_aggregate ::= create_aggregate_normal | create_aggregate_order_by | create_aggregate_old create_aggregate_normal ::= CREATE AGGREGATE aggregate_name ( { aggregate_arg [ , ... ] | * } ) ( SFUNC = sfunc , STYPE = state_data_type [ , aggregate_normal_option [ ... ] ] ) create_aggregate_order_by ::= CREATE AGGREGATE aggregate_name ( [ aggregate_arg [ , ... ] ] ORDER BY aggregate_arg [ , ... ] ) ( SFUNC = sfunc , STYPE = state_data_type [ , aggregate_order_by_option [ ... ] ] ) create_aggregate_old ::= CREATE AGGREGATE aggregate_name ( BASETYPE = base_type , SFUNC = sfunc , STYPE = state_data_type [ , aggregate_old_option [ ... ] ] ) aggregate_arg ::= [ aggregate_arg_mode ] [ formal_arg ] arg_type aggregate_normal_option ::= SSPACE = state_data_size | FINALFUNC = ffunc | FINALFUNC_EXTRA | FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } | COMBINEFUNC = combinefunc | SERIALFUNC = serialfunc | DESERIALFUNC = deserialfunc | INITCOND = initial_condition | MSFUNC = msfunc | MINVFUNC = minvfunc | MSTYPE = mstate_data_type | MSSPACE = mstate_data_size | MFINALFUNC = mffunc | MFINALFUNC_EXTRA | MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } | MINITCOND = minitial_condition | SORTOP = sort_operator | PARALLEL = { SAFE | RESTRICTED | UNSAFE } aggregate_order_by_option ::= SSPACE = state_data_size | FINALFUNC = ffunc | FINALFUNC_EXTRA | FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } | INITCOND = initial_condition | PARALLEL = { SAFE | RESTRICTED | UNSAFE } | HYPOTHETICAL aggregate_old_option ::= SSPACE = state_data_size | FINALFUNC = ffunc | FINALFUNC_EXTRA | FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } | COMBINEFUNC = combinefunc | SERIALFUNC = serialfunc | DESERIALFUNC = deserialfunc | INITCOND = initial_condition | MSFUNC = msfunc | MINVFUNC = minvfunc | MSTYPE = mstate_data_type | MSSPACE = mstate_data_size | MFINALFUNC = mffunc | MFINALFUNC_EXTRA | MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } | MINITCOND = minitial_condition | SORTOP = sort_operator

create_aggregate

create_aggregate_normalcreate_aggregate_order_bycreate_aggregate_old

create_aggregate_normal

CREATEAGGREGATEaggregate_name(,aggregate_arg*)(SFUNC=sfunc,STYPE=state_data_type,aggregate_normal_option)

create_aggregate_order_by

CREATEAGGREGATEaggregate_name(,aggregate_argORDERBY,aggregate_arg)(SFUNC=sfunc,STYPE=state_data_type,aggregate_order_by_option)

create_aggregate_old

CREATEAGGREGATEaggregate_name(BASETYPE=base_type,SFUNC=sfunc,STYPE=state_data_type,aggregate_old_option)

aggregate_arg

aggregate_arg_modeformal_argarg_type

aggregate_normal_option

SSPACE=state_data_sizeFINALFUNC=ffuncFINALFUNC_EXTRAFINALFUNC_MODIFY=READ_ONLYSHAREABLEREAD_WRITECOMBINEFUNC=combinefuncSERIALFUNC=serialfuncDESERIALFUNC=deserialfuncINITCOND=initial_conditionMSFUNC=msfuncMINVFUNC=minvfuncMSTYPE=mstate_data_typeMSSPACE=mstate_data_sizeMFINALFUNC=mffuncMFINALFUNC_EXTRAMFINALFUNC_MODIFY=READ_ONLYSHAREABLEREAD_WRITEMINITCOND=minitial_conditionSORTOP=sort_operatorPARALLEL=SAFERESTRICTEDUNSAFE

aggregate_order_by_option

SSPACE=state_data_sizeFINALFUNC=ffuncFINALFUNC_EXTRAFINALFUNC_MODIFY=READ_ONLYSHAREABLEREAD_WRITEINITCOND=initial_conditionPARALLEL=SAFERESTRICTEDUNSAFEHYPOTHETICAL

aggregate_old_option

SSPACE=state_data_sizeFINALFUNC=ffuncFINALFUNC_EXTRAFINALFUNC_MODIFY=READ_ONLYSHAREABLEREAD_WRITECOMBINEFUNC=combinefuncSERIALFUNC=serialfuncDESERIALFUNC=deserialfuncINITCOND=initial_conditionMSFUNC=msfuncMINVFUNC=minvfuncMSTYPE=mstate_data_typeMSSPACE=mstate_data_sizeMFINALFUNC=mffuncMFINALFUNC_EXTRAMFINALFUNC_MODIFY=READ_ONLYSHAREABLEREAD_WRITEMINITCOND=minitial_conditionSORTOP=sort_operator

Semantics

The order of options does not matter. Even the mandatory options BASETYPE, SFUNC, and STYPE may appear in any order.

See the semantics of each option in the [PostgreSQL docs][postgresql-docs-create-aggregate].

Examples

Normal syntax example.

yugabyte=# CREATE AGGREGATE sumdouble (float8) ( STYPE = float8, SFUNC = float8pl, MSTYPE = float8, MSFUNC = float8pl, MINVFUNC = float8mi ); yugabyte=# CREATE TABLE normal_table( f float8, i int ); yugabyte=# INSERT INTO normal_table(f, i) VALUES (0.1, 9), (0.9, 1); yugabyte=# SELECT sumdouble(f), sumdouble(i) FROM normal_table;

Order by syntax example.

yugabyte=# CREATE AGGREGATE my_percentile_disc(float8 ORDER BY anyelement) ( STYPE = internal, SFUNC = ordered_set_transition, FINALFUNC = percentile_disc_final, FINALFUNC_EXTRA = true, FINALFUNC_MODIFY = read_write ); yugabyte=# SELECT my_percentile_disc(0.1), my_percentile_disc(0.9) WITHIN GROUP (ORDER BY typlen) FROM pg_type;

Old syntax example.

yugabyte=# CREATE AGGREGATE oldcnt( SFUNC = int8inc, BASETYPE = 'ANY', STYPE = int8, INITCOND = '0' ); yugabyte=# SELECT oldcnt(*) FROM pg_aggregate;

Zero-argument aggregate example.

yugabyte=# CREATE AGGREGATE newcnt(*) ( SFUNC = int8inc, STYPE = int8, INITCOND = '0', PARALLEL = SAFE ); yugabyte=# SELECT newcnt(*) FROM pg_aggregate;

See also