CREATE AGGREGATE

This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

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