CREATE AGGREGATE
Synopsis
Use the CREATE AGGREGATE statement to create an aggregate function. There are three ways to
create aggregates.
Syntax
Grammar
Diagramcreate_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
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;