Aggregate

An Aggregate function computes a single result from a set of input values.

Properties

Property	Type	Required	Description
name	string	yes	The name of the aggregate function to create.
schema	string	yes	The schema to create the aggregate function in.
owner	string	—	The role that owns the aggregate function.
sql	string	—	User-provided raw SQL snippet.
arguments	array of argument	—	An array of arguments that are passed into the function.
order_by	array of argument	—	An array of arguments that are passed into the function for ordered-set behavior.
sfunc	string	—	The name of the state transition function to be called for each input row.
state_data_type	string	—	The data type for the aggregate's state value.
state_data_size	integer	—	The approximate average size (in bytes) of the aggregate's state value.
ffunc	string	—	The name of the final function called to compute the aggregate's result after all input rows have been traversed.
finalfunc_extra	boolean	—	If true then in addition to the final state value and any direct arguments, the final function receives extra NULL values corresponding to the aggregate's regular (aggregated) arguments. This is mainly useful to allow correct resolution of the aggregate result type when a polymorphic aggregate is being defined.
finalfunc_modify	READ_ONLY | SHAREABLE | READ_WRITE	—	This option specifies whether the final function is a pure function that does not modify its arguments. READ_ONLY indicates it does not; the other two values indicate that it may change the transition state value.
combinefunc	string	—	A function may optionally be specified to allow the aggregate function to support partial aggregation.
serialfunc	string	—	An aggregate function whose state_data_type is internal can participate in parallel aggregation only if it has a serialfunc function, which must serialize the aggregate state into a bytea value for transmission to another process. This function must take a single argument of type internal and return type bytea. A corresponding deserialfunc is also required.
deserialfunc	string	—	Deserialize a previously serialized aggregate state back into state_data_type. This function must take two arguments of types bytea and internal, and produce a result of type internal. (Note: the second, internal argument is unused, but is required for type safety reasons.).
initial_condition	string	—	The initial setting for the state value.
msfunc	string	—	The name of the forward state transition function to be called for each input row in moving-aggregate mode. This is exactly like the regular transition function, except that its first argument and result are of type mstate_data_type, which might be different from state_data_type.
minvfunc	string	—	The name of the inverse state transition function to be used in moving-aggregate mode. This function has the same argument and result types as msfunc, but it is used to remove a value from the current aggregate state, rather than add a value to it. The inverse transition function must have the same strictness attribute as the forward state transition function.
mstate_data_type	string	—	The data type for the aggregate's state value, when using moving-aggregate mode.
mstate_data_size	integer	—	The approximate average size (in bytes) of the aggregate's state value, when using moving-aggregate mode. This works the same as state_data_size.
mffunc	string	—	The name of the final function called to compute the aggregate's result after all input rows have been traversed, when using moving-aggregate mode.
mfinalfunc_extra	boolean	—	Include extra dummy arguments.
mfinalfunc_modify	READ_ONLY | SHAREABLE | READ_WRITE	—	This option specifies whether the Moving Average final function is a pure function that does not modify its arguments. READ_ONLY indicates it does not; the other two values indicate that it may change the transition state value.
minitial_condition	string	—	The initial setting for the state value, when using moving-aggregate mode.
sort_operator	string	—	The associated sort operator for a MIN- or MAX-like aggregate. This is just an operator name (possibly schema-qualified). The operator is assumed to have the same input data types as the aggregate (which must be a single-argument normal aggregate).
parallel	SAFE | RESTRICTED | UNSAFE	—	An aggregate will not be considered for parallelization if it is marked PARALLEL UNSAFE (which is the default!) or PARALLEL RESTRICTED. Note that the parallel-safety markings of the aggregate's support functions are not consulted by the planner, only the marking of the aggregate itself.
hypothetical	boolean	—	For ordered-set aggregates only, this flag specifies that the aggregate arguments are to be processed according to the requirements for hypothetical-set aggregates: that is, the last few direct arguments must match the data types of the aggregated (WITHIN GROUP) arguments. The HYPOTHETICAL flag has no effect on run-time behavior, only on parse-time resolution of the data types and collations of the aggregate's arguments.
comment	string	—	An optional comment about the aggregate.
dependencies	dependencies	—	Database objects this object is dependent upon.

Mutually exclusive forms

Exactly one of these must hold:

• sql, without arguments or sfunc or state_data_type or state_data_size or ffunc or finalfunc_extra or finalfunc_modify or combinefunc or serialfunc or deserialfunc or initial_condition or msfunc or minvfunc or mstate_data_type or mstate_data_size or mffunc or mfinalfunc_extra or mfinalfunc_modify or minitial_condition or sort_operator or parallel or hypothetical
• arguments and sfunc and state_data_type, without sql

---

Source: schemata/aggregate.yml · Resolved JSON Schema: aggregate.json