3.11. Aggregates

Aggregate functions are objects whose main goal is to compute a single result from a set of input values. PostgreSQL offers a good set of built-in aggregates but due to the extensibility of the database system, the user can create custom aggregates as well. The main attributes of this kind of object are described below.

1234

Functions
#	Field	Description
1	Transition function	The state transition function, used to compute the current aggregate result for each input row. This function can have more than one argument, but the first one, as well as the return type of the function, must be the same as the state data type configured in the tab State.
2	Final function	The function used to compute the final result of the aggregate when all the input has been processed. This function must take a single argument of the same type as the state data type configured in the tab State. The return type of the aggregate is the return type of this function.
3	Sort operator	An optional sort operator used by aggregates that work like the MIN and MAX functions. The input data type of the operator must be the same as the one configured for the aggregate itself.
4	Initial condition	The initial value of the state data type configured in the tab State. The value must be written in the format accepted by that data type.

1234

Inputs
#	Field	Group box	Description
1	Type	Input data type	The input data type to be added to the list. To create a zero-argument aggregate (e.g. count(*)), leave the list empty.
2	Dimension	Input data type	Number of dimensions when the type is an array (type[]). 0 means that the type is not an array.
3	Format	Input data type	Read-only preview of the data type as it is written in the SQL code, with length, precision, dimension and other modifiers applied.
4	Input data types		The set of input data types accepted by the aggregate.

123

State
#	Field	Group box	Description
1	Type	State data type	The state data type of the aggregate. It is used to create a temporary variable while the aggregate is running over the set of rows.
2	Dimension	State data type	Number of dimensions when the type is an array (type[]). 0 means that the type is not an array.
3	Format	State data type	Read-only preview of the data type as it is written in the SQL code, with length, precision, dimension and other modifiers applied.

References

Aggregate DDL
https://www.postgresql.org/docs/current/static/sql-createaggregate.html

Built-in aggregates
https://www.postgresql.org/docs/current/static/functions-aggregate.html