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.
| 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 the 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 that is 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 the Function State tab as well for the function's return type. |
Sort Operator | An optional sort operator used by 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 the 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