3.11. Aggregates
Aggregate functions are objects which 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 is able to create custom aggregates. The main attributes of this kind of object is described below.
| Attribute | Description |
|---|---|
Final Function | The function used to compute the final result of the aggregate when all input was processed. This function must take a single argument of the same type as the one configured on Function State tab. The return type of the aggregate will be the same as the return type of this function. |
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 must be the same as the one configured on Function State tab as well for the function's return type. |
Sort Operator | An optional sort operator used for aggregates that works like MIN and MAX functions. The input data type for the operator must be the same as the configured for the aggregate itself. |
Initial Condition | This attribute stores the initial value for the state data type configured on Function State tab. The value informed here must be formatted according to the way accepted by the data type. |
Function Inputs | This tab configures the set of input data types accepted by the aggregate. To create a zero-argument aggregate (e.g. count(*)) leave this field empty. |
Function State | Configures the state data type for the aggregate. This data type is used to create a temporary variable while the aggregate is running over the set of rows. |
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