Home > APIs > YSQL > The SQL language > SQL statements >

CREATE AGGREGATE

Synopsis

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

Syntax

Grammar
Diagram

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_argmode ] [ argname ] argtype

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_normal

create_aggregate_order_by

create_aggregate_old

aggregate_arg

aggregate_normal_option

aggregate_order_by_option

aggregate_old_option

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;