1CREATE AGGREGATE(7) PostgreSQL 9.2.24 Documentation CREATE AGGREGATE(7)
2
6 CREATE_AGGREGATE - define a new aggregate function
7
9 CREATE AGGREGATE name ( input_data_type [ , ... ] ) (
10 SFUNC = sfunc,
11 STYPE = state_data_type
12 [ , FINALFUNC = ffunc ]
13 [ , INITCOND = initial_condition ]
14 [ , SORTOP = sort_operator ]
15 )
16 or the old syntax
18 CREATE AGGREGATE name (
20 BASETYPE = base_type,
21 SFUNC = sfunc,
22 STYPE = state_data_type
23 [ , FINALFUNC = ffunc ]
24 [ , INITCOND = initial_condition ]
25 [ , SORTOP = sort_operator ]
26 )
27
29 CREATE AGGREGATE defines a new aggregate function. Some basic and
30 commonly-used aggregate functions are included with the distribution;
31 they are documented in Section 9.20, “Aggregate Functions”, in the
32 documentation. If one defines new types or needs an aggregate function
33 not already provided, then CREATE AGGREGATE can be used to provide the
34 desired features.
35 If a schema name is given (for example, CREATE AGGREGATE myschema.myagg
37 ...) then the aggregate function is created in the specified schema.
38 Otherwise it is created in the current schema.
39 An aggregate function is identified by its name and input data type(s).
41 Two aggregates in the same schema can have the same name if they
42 operate on different input types. The name and input data type(s) of an
43 aggregate must also be distinct from the name and input data type(s) of
44 every ordinary function in the same schema.
45 An aggregate function is made from one or two ordinary functions: a
47 state transition function sfunc, and an optional final calculation
48 function ffunc. These are used as follows:
49 sfunc( internal-state, next-data-values ) ---> next-internal-state
51 ffunc( internal-state ) ---> aggregate-value
52 PostgreSQL creates a temporary variable of data type stype to hold the
54 current internal state of the aggregate. At each input row, the
55 aggregate argument value(s) are calculated and the state transition
56 function is invoked with the current state value and the new argument
57 value(s) to calculate a new internal state value. After all the rows
58 have been processed, the final function is invoked once to calculate
59 the aggregate's return value. If there is no final function then the
60 ending state value is returned as-is.
61 An aggregate function can provide an initial condition, that is, an
63 initial value for the internal state value. This is specified and
64 stored in the database as a value of type text, but it must be a valid
65 external representation of a constant of the state value data type. If
66 it is not supplied then the state value starts out null.
67 If the state transition function is declared “strict”, then it cannot
69 be called with null inputs. With such a transition function, aggregate
70 execution behaves as follows. Rows with any null input values are
71 ignored (the function is not called and the previous state value is
72 retained). If the initial state value is null, then at the first row
73 with all-nonnull input values, the first argument value replaces the
74 state value, and the transition function is invoked at subsequent rows
75 with all-nonnull input values. This is handy for implementing
76 aggregates like max. Note that this behavior is only available when
77 state_data_type is the same as the first input_data_type. When these
78 types are different, you must supply a nonnull initial condition or use
79 a nonstrict transition function.
80 If the state transition function is not strict, then it will be called
82 unconditionally at each input row, and must deal with null inputs and
83 null transition values for itself. This allows the aggregate author to
84 have full control over the aggregate's handling of null values.
85 If the final function is declared “strict”, then it will not be called
87 when the ending state value is null; instead a null result will be
88 returned automatically. (Of course this is just the normal behavior of
89 strict functions.) In any case the final function has the option of
90 returning a null value. For example, the final function for avg returns
91 null when it sees there were zero input rows.
92 Aggregates that behave like MIN or MAX can sometimes be optimized by
94 looking into an index instead of scanning every input row. If this
95 aggregate can be so optimized, indicate it by specifying a sort
96 operator. The basic requirement is that the aggregate must yield the
97 first element in the sort ordering induced by the operator; in other
98 words:
99 SELECT agg(col) FROM tab;
101 must be equivalent to:
103 SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
105 Further assumptions are that the aggregate ignores null inputs, and
107 that it delivers a null result if and only if there were no non-null
108 inputs. Ordinarily, a data type's < operator is the proper sort
109 operator for MIN, and > is the proper sort operator for MAX. Note that
110 the optimization will never actually take effect unless the specified
111 operator is the “less than” or “greater than” strategy member of a
112 B-tree index operator class.
113 To be able to create an aggregate function, you must have USAGE
115 privilege on the argument types, the state type, and the return type,
116 as well as EXECUTE privilege on the transition and final functions.
117
119 name
120 The name (optionally schema-qualified) of the aggregate function to
121 create.
122 input_data_type
124 An input data type on which this aggregate function operates. To
125 create a zero-argument aggregate function, write * in place of the
126 list of input data types. (An example of such an aggregate is
127 count(*).)
128 base_type
130 In the old syntax for CREATE AGGREGATE, the input data type is
131 specified by a basetype parameter rather than being written next to
132 the aggregate name. Note that this syntax allows only one input
133 parameter. To define a zero-argument aggregate function, specify
134 the basetype as "ANY" (not *).
135 sfunc
137 The name of the state transition function to be called for each
138 input row. For an N-argument aggregate function, the sfunc must
139 take N+1 arguments, the first being of type state_data_type and the
140 rest matching the declared input data type(s) of the aggregate. The
141 function must return a value of type state_data_type. This function
142 takes the current state value and the current input data value(s),
143 and returns the next state value.
144 state_data_type
146 The data type for the aggregate's state value.
147 ffunc
149 The name of the final function called to compute the aggregate's
150 result after all input rows have been traversed. The function must
151 take a single argument of type state_data_type. The return data
152 type of the aggregate is defined as the return type of this
153 function. If ffunc is not specified, then the ending state value is
154 used as the aggregate's result, and the return type is
155 state_data_type.
156 initial_condition
158 The initial setting for the state value. This must be a string
159 constant in the form accepted for the data type state_data_type. If
160 not specified, the state value starts out null.
161 sort_operator
163 The associated sort operator for a MIN- or MAX-like aggregate. This
164 is just an operator name (possibly schema-qualified). The operator
165 is assumed to have the same input data types as the aggregate
166 (which must be a single-argument aggregate).
167 The parameters of CREATE AGGREGATE can be written in any order, not
169 just the order illustrated above.
170
172 See Section 35.10, “User-defined Aggregates”, in the documentation.
173
175 CREATE AGGREGATE is a PostgreSQL language extension. The SQL standard
176 does not provide for user-defined aggregate functions.
177
179 ALTER AGGREGATE (ALTER_AGGREGATE(7)), DROP AGGREGATE
180 (DROP_AGGREGATE(7))
181PostgreSQL 9.2.24 2017-11-06 CREATE AGGREGATE(7)