| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/create_aggregate.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-createaggregate"> |
| 7 |
<indexterm zone="sql-createaggregate"> |
| 8 |
<primary>CREATE AGGREGATE</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle>CREATE AGGREGATE</refentrytitle> |
| 13 |
<manvolnum>7</manvolnum> |
| 14 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>CREATE AGGREGATE</refname> |
| 19 |
<refpurpose>define a new aggregate function</refpurpose> |
| 20 |
</refnamediv> |
| 21 |
|
| 22 |
<refsynopsisdiv> |
| 23 |
<synopsis> |
| 24 |
CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable> ( [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">arg_data_type</replaceable> [ , ... ] ) ( |
| 25 |
SFUNC = <replaceable class="parameter">sfunc</replaceable>, |
| 26 |
STYPE = <replaceable class="parameter">state_data_type</replaceable> |
| 27 |
[ , SSPACE = <replaceable class="parameter">state_data_size</replaceable> ] |
| 28 |
[ , FINALFUNC = <replaceable class="parameter">ffunc</replaceable> ] |
| 29 |
[ , FINALFUNC_EXTRA ] |
| 30 |
[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] |
| 31 |
[ , COMBINEFUNC = <replaceable class="parameter">combinefunc</replaceable> ] |
| 32 |
[ , SERIALFUNC = <replaceable class="parameter">serialfunc</replaceable> ] |
| 33 |
[ , DESERIALFUNC = <replaceable class="parameter">deserialfunc</replaceable> ] |
| 34 |
[ , INITCOND = <replaceable class="parameter">initial_condition</replaceable> ] |
| 35 |
[ , MSFUNC = <replaceable class="parameter">msfunc</replaceable> ] |
| 36 |
[ , MINVFUNC = <replaceable class="parameter">minvfunc</replaceable> ] |
| 37 |
[ , MSTYPE = <replaceable class="parameter">mstate_data_type</replaceable> ] |
| 38 |
[ , MSSPACE = <replaceable class="parameter">mstate_data_size</replaceable> ] |
| 39 |
[ , MFINALFUNC = <replaceable class="parameter">mffunc</replaceable> ] |
| 40 |
[ , MFINALFUNC_EXTRA ] |
| 41 |
[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] |
| 42 |
[ , MINITCOND = <replaceable class="parameter">minitial_condition</replaceable> ] |
| 43 |
[ , SORTOP = <replaceable class="parameter">sort_operator</replaceable> ] |
| 44 |
[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] |
| 45 |
) |
| 46 |
|
| 47 |
CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">arg_data_type</replaceable> [ , ... ] ] |
| 48 |
ORDER BY [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">arg_data_type</replaceable> [ , ... ] ) ( |
| 49 |
SFUNC = <replaceable class="parameter">sfunc</replaceable>, |
| 50 |
STYPE = <replaceable class="parameter">state_data_type</replaceable> |
| 51 |
[ , SSPACE = <replaceable class="parameter">state_data_size</replaceable> ] |
| 52 |
[ , FINALFUNC = <replaceable class="parameter">ffunc</replaceable> ] |
| 53 |
[ , FINALFUNC_EXTRA ] |
| 54 |
[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] |
| 55 |
[ , INITCOND = <replaceable class="parameter">initial_condition</replaceable> ] |
| 56 |
[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] |
| 57 |
[ , HYPOTHETICAL ] |
| 58 |
) |
| 59 |
|
| 60 |
<phrase>or the old syntax</phrase> |
| 61 |
|
| 62 |
CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable> ( |
| 63 |
BASETYPE = <replaceable class="parameter">base_type</replaceable>, |
| 64 |
SFUNC = <replaceable class="parameter">sfunc</replaceable>, |
| 65 |
STYPE = <replaceable class="parameter">state_data_type</replaceable> |
| 66 |
[ , SSPACE = <replaceable class="parameter">state_data_size</replaceable> ] |
| 67 |
[ , FINALFUNC = <replaceable class="parameter">ffunc</replaceable> ] |
| 68 |
[ , FINALFUNC_EXTRA ] |
| 69 |
[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] |
| 70 |
[ , COMBINEFUNC = <replaceable class="parameter">combinefunc</replaceable> ] |
| 71 |
[ , SERIALFUNC = <replaceable class="parameter">serialfunc</replaceable> ] |
| 72 |
[ , DESERIALFUNC = <replaceable class="parameter">deserialfunc</replaceable> ] |
| 73 |
[ , INITCOND = <replaceable class="parameter">initial_condition</replaceable> ] |
| 74 |
[ , MSFUNC = <replaceable class="parameter">msfunc</replaceable> ] |
| 75 |
[ , MINVFUNC = <replaceable class="parameter">minvfunc</replaceable> ] |
| 76 |
[ , MSTYPE = <replaceable class="parameter">mstate_data_type</replaceable> ] |
| 77 |
[ , MSSPACE = <replaceable class="parameter">mstate_data_size</replaceable> ] |
| 78 |
[ , MFINALFUNC = <replaceable class="parameter">mffunc</replaceable> ] |
| 79 |
[ , MFINALFUNC_EXTRA ] |
| 80 |
[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] |
| 81 |
[ , MINITCOND = <replaceable class="parameter">minitial_condition</replaceable> ] |
| 82 |
[ , SORTOP = <replaceable class="parameter">sort_operator</replaceable> ] |
| 83 |
) |
| 84 |
</synopsis> |
| 85 |
</refsynopsisdiv> |
| 86 |
|
| 87 |
<refsect1> |
| 88 |
<title>Description</title> |
| 89 |
|
| 90 |
<para> |
| 91 |
<command>CREATE AGGREGATE</command> defines a new aggregate function. |
| 92 |
<command>CREATE OR REPLACE AGGREGATE</command> will either define a new |
| 93 |
aggregate function or replace an existing definition. Some basic and |
| 94 |
commonly-used aggregate functions are included with the distribution; they |
| 95 |
are documented in <xref linkend="functions-aggregate"/>. If one defines new |
| 96 |
types or needs an aggregate function not already provided, then |
| 97 |
<command>CREATE AGGREGATE</command> can be used to provide the desired |
| 98 |
features. |
| 99 |
</para> |
| 100 |
|
| 101 |
<para> |
| 102 |
When replacing an existing definition, the argument types, result type, |
| 103 |
and number of direct arguments may not be changed. Also, the new definition |
| 104 |
must be of the same kind (ordinary aggregate, ordered-set aggregate, or |
| 105 |
hypothetical-set aggregate) as the old one. |
| 106 |
</para> |
| 107 |
|
| 108 |
<para> |
| 109 |
If a schema name is given (for example, <literal>CREATE AGGREGATE |
| 110 |
myschema.myagg ...</literal>) then the aggregate function is created in the |
| 111 |
specified schema. Otherwise it is created in the current schema. |
| 112 |
</para> |
| 113 |
|
| 114 |
<para> |
| 115 |
An aggregate function is identified by its name and input data type(s). |
| 116 |
Two aggregates in the same schema can have the same name if they operate on |
| 117 |
different input types. The |
| 118 |
name and input data type(s) of an aggregate must also be distinct from |
| 119 |
the name and input data type(s) of every ordinary function in the same |
| 120 |
schema. |
| 121 |
This behavior is identical to overloading of ordinary function names |
| 122 |
(see <xref linkend="sql-createfunction"/>). |
| 123 |
</para> |
| 124 |
|
| 125 |
<para> |
| 126 |
A simple aggregate function is made from one or two ordinary |
| 127 |
functions: |
| 128 |
a state transition function |
| 129 |
<replaceable class="parameter">sfunc</replaceable>, |
| 130 |
and an optional final calculation function |
| 131 |
<replaceable class="parameter">ffunc</replaceable>. |
| 132 |
These are used as follows: |
| 133 |
<programlisting> |
| 134 |
<replaceable class="parameter">sfunc</replaceable>( internal-state, next-data-values ) ---> next-internal-state |
| 135 |
<replaceable class="parameter">ffunc</replaceable>( internal-state ) ---> aggregate-value |
| 136 |
</programlisting> |
| 137 |
</para> |
| 138 |
|
| 139 |
<para> |
| 140 |
<productname>PostgreSQL</productname> creates a temporary variable |
| 141 |
of data type <replaceable class="parameter">stype</replaceable> |
| 142 |
to hold the current internal state of the aggregate. At each input row, |
| 143 |
the aggregate argument value(s) are calculated and |
| 144 |
the state transition function is invoked with the current state value |
| 145 |
and the new argument value(s) to calculate a new |
| 146 |
internal state value. After all the rows have been processed, |
| 147 |
the final function is invoked once to calculate the aggregate's return |
| 148 |
value. If there is no final function then the ending state value |
| 149 |
is returned as-is. |
| 150 |
</para> |
| 151 |
|
| 152 |
<para> |
| 153 |
An aggregate function can provide an initial condition, |
| 154 |
that is, an initial value for the internal state value. |
| 155 |
This is specified and stored in the database as a value of type |
| 156 |
<type>text</type>, but it must be a valid external representation |
| 157 |
of a constant of the state value data type. If it is not supplied |
| 158 |
then the state value starts out null. |
| 159 |
</para> |
| 160 |
|
| 161 |
<para> |
| 162 |
If the state transition function is declared <quote>strict</quote>, |
| 163 |
then it cannot be called with null inputs. With such a transition |
| 164 |
function, aggregate execution behaves as follows. Rows with any null input |
| 165 |
values are ignored (the function is not called and the previous state value |
| 166 |
is retained). If the initial state value is null, then at the first row |
| 167 |
with all-nonnull input values, the first argument value replaces the state |
| 168 |
value, and the transition function is invoked at each subsequent row with |
| 169 |
all-nonnull input values. |
| 170 |
This is handy for implementing aggregates like <function>max</function>. |
| 171 |
Note that this behavior is only available when |
| 172 |
<replaceable class="parameter">state_data_type</replaceable> |
| 173 |
is the same as the first |
| 174 |
<replaceable class="parameter">arg_data_type</replaceable>. |
| 175 |
When these types are different, you must supply a nonnull initial |
| 176 |
condition or use a nonstrict transition function. |
| 177 |
</para> |
| 178 |
|
| 179 |
<para> |
| 180 |
If the state transition function is not strict, then it will be called |
| 181 |
unconditionally at each input row, and must deal with null inputs |
| 182 |
and null state values for itself. This allows the aggregate |
| 183 |
author to have full control over the aggregate's handling of null values. |
| 184 |
</para> |
| 185 |
|
| 186 |
<para> |
| 187 |
If the final function is declared <quote>strict</quote>, then it will not |
| 188 |
be called when the ending state value is null; instead a null result |
| 189 |
will be returned automatically. (Of course this is just the normal |
| 190 |
behavior of strict functions.) In any case the final function has |
| 191 |
the option of returning a null value. For example, the final function for |
| 192 |
<function>avg</function> returns null when it sees there were zero |
| 193 |
input rows. |
| 194 |
</para> |
| 195 |
|
| 196 |
<para> |
| 197 |
Sometimes it is useful to declare the final function as taking not just |
| 198 |
the state value, but extra parameters corresponding to the aggregate's |
| 199 |
input values. The main reason for doing this is if the final function |
| 200 |
is polymorphic and the state value's data type would be inadequate to |
| 201 |
pin down the result type. These extra parameters are always passed as |
| 202 |
NULL (and so the final function must not be strict when |
| 203 |
the <literal>FINALFUNC_EXTRA</literal> option is used), but nonetheless they |
| 204 |
are valid parameters. The final function could for example make use |
| 205 |
of <function>get_fn_expr_argtype</function> to identify the actual argument type |
| 206 |
in the current call. |
| 207 |
</para> |
| 208 |
|
| 209 |
<para> |
| 210 |
An aggregate can optionally support <firstterm>moving-aggregate mode</firstterm>, |
| 211 |
as described in <xref linkend="xaggr-moving-aggregates"/>. This requires |
| 212 |
specifying the <literal>MSFUNC</literal>, <literal>MINVFUNC</literal>, |
| 213 |
and <literal>MSTYPE</literal> parameters, and optionally |
| 214 |
the <literal>MSSPACE</literal>, <literal>MFINALFUNC</literal>, |
| 215 |
<literal>MFINALFUNC_EXTRA</literal>, <literal>MFINALFUNC_MODIFY</literal>, |
| 216 |
and <literal>MINITCOND</literal> parameters. Except for <literal>MINVFUNC</literal>, |
| 217 |
these parameters work like the corresponding simple-aggregate parameters |
| 218 |
without <literal>M</literal>; they define a separate implementation of the |
| 219 |
aggregate that includes an inverse transition function. |
| 220 |
</para> |
| 221 |
|
| 222 |
<para> |
| 223 |
The syntax with <literal>ORDER BY</literal> in the parameter list creates |
| 224 |
a special type of aggregate called an <firstterm>ordered-set |
| 225 |
aggregate</firstterm>; or if <literal>HYPOTHETICAL</literal> is specified, then |
| 226 |
a <firstterm>hypothetical-set aggregate</firstterm> is created. These |
| 227 |
aggregates operate over groups of sorted values in order-dependent ways, |
| 228 |
so that specification of an input sort order is an essential part of a |
| 229 |
call. Also, they can have <firstterm>direct</firstterm> arguments, which are |
| 230 |
arguments that are evaluated only once per aggregation rather than once |
| 231 |
per input row. Hypothetical-set aggregates are a subclass of ordered-set |
| 232 |
aggregates in which some of the direct arguments are required to match, |
| 233 |
in number and data types, the aggregated argument columns. This allows |
| 234 |
the values of those direct arguments to be added to the collection of |
| 235 |
aggregate-input rows as an additional <quote>hypothetical</quote> row. |
| 236 |
</para> |
| 237 |
|
| 238 |
<para> |
| 239 |
An aggregate can optionally support <firstterm>partial aggregation</firstterm>, |
| 240 |
as described in <xref linkend="xaggr-partial-aggregates"/>. |
| 241 |
This requires specifying the <literal>COMBINEFUNC</literal> parameter. |
| 242 |
If the <replaceable class="parameter">state_data_type</replaceable> |
| 243 |
is <type>internal</type>, it's usually also appropriate to provide the |
| 244 |
<literal>SERIALFUNC</literal> and <literal>DESERIALFUNC</literal> parameters so that |
| 245 |
parallel aggregation is possible. Note that the aggregate must also be |
| 246 |
marked <literal>PARALLEL SAFE</literal> to enable parallel aggregation. |
| 247 |
</para> |
| 248 |
|
| 249 |
<para> |
| 250 |
Aggregates that behave like <function>MIN</function> or <function>MAX</function> can |
| 251 |
sometimes be optimized by looking into an index instead of scanning every |
| 252 |
input row. If this aggregate can be so optimized, indicate it by |
| 253 |
specifying a <firstterm>sort operator</firstterm>. The basic requirement is that |
| 254 |
the aggregate must yield the first element in the sort ordering induced by |
| 255 |
the operator; in other words: |
| 256 |
<programlisting> |
| 257 |
SELECT agg(col) FROM tab; |
| 258 |
</programlisting> |
| 259 |
must be equivalent to: |
| 260 |
<programlisting> |
| 261 |
SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; |
| 262 |
</programlisting> |
| 263 |
Further assumptions are that the aggregate ignores null inputs, and that |
| 264 |
it delivers a null result if and only if there were no non-null inputs. |
| 265 |
Ordinarily, a data type's <literal><</literal> operator is the proper sort |
| 266 |
operator for <function>MIN</function>, and <literal>></literal> is the proper sort |
| 267 |
operator for <function>MAX</function>. Note that the optimization will never |
| 268 |
actually take effect unless the specified operator is the <quote>less |
| 269 |
than</quote> or <quote>greater than</quote> strategy member of a B-tree |
| 270 |
index operator class. |
| 271 |
</para> |
| 272 |
|
| 273 |
<para> |
| 274 |
To be able to create an aggregate function, you must |
| 275 |
have <literal>USAGE</literal> privilege on the argument types, the state |
| 276 |
type(s), and the return type, as well as <literal>EXECUTE</literal> |
| 277 |
privilege on the supporting functions. |
| 278 |
</para> |
| 279 |
</refsect1> |
| 280 |
|
| 281 |
<refsect1> |
| 282 |
<title>Parameters</title> |
| 283 |
|
| 284 |
<variablelist> |
| 285 |
<varlistentry> |
| 286 |
<term><replaceable class="parameter">name</replaceable></term> |
| 287 |
<listitem> |
| 288 |
<para> |
| 289 |
The name (optionally schema-qualified) of the aggregate function |
| 290 |
to create. |
| 291 |
</para> |
| 292 |
</listitem> |
| 293 |
</varlistentry> |
| 294 |
|
| 295 |
<varlistentry> |
| 296 |
<term><replaceable class="parameter">argmode</replaceable></term> |
| 297 |
|
| 298 |
<listitem> |
| 299 |
<para> |
| 300 |
The mode of an argument: <literal>IN</literal> or <literal>VARIADIC</literal>. |
| 301 |
(Aggregate functions do not support <literal>OUT</literal> arguments.) |
| 302 |
If omitted, the default is <literal>IN</literal>. Only the last argument |
| 303 |
can be marked <literal>VARIADIC</literal>. |
| 304 |
</para> |
| 305 |
</listitem> |
| 306 |
</varlistentry> |
| 307 |
|
| 308 |
<varlistentry> |
| 309 |
<term><replaceable class="parameter">argname</replaceable></term> |
| 310 |
|
| 311 |
<listitem> |
| 312 |
<para> |
| 313 |
The name of an argument. This is currently only useful for |
| 314 |
documentation purposes. If omitted, the argument has no name. |
| 315 |
</para> |
| 316 |
</listitem> |
| 317 |
</varlistentry> |
| 318 |
|
| 319 |
<varlistentry> |
| 320 |
<term><replaceable class="parameter">arg_data_type</replaceable></term> |
| 321 |
<listitem> |
| 322 |
<para> |
| 323 |
An input data type on which this aggregate function operates. |
| 324 |
To create a zero-argument aggregate function, write <literal>*</literal> |
| 325 |
in place of the list of argument specifications. (An example of such an |
| 326 |
aggregate is <function>count(*)</function>.) |
| 327 |
</para> |
| 328 |
</listitem> |
| 329 |
</varlistentry> |
| 330 |
|
| 331 |
<varlistentry> |
| 332 |
<term><replaceable class="parameter">base_type</replaceable></term> |
| 333 |
<listitem> |
| 334 |
<para> |
| 335 |
In the old syntax for <command>CREATE AGGREGATE</command>, the input data type |
| 336 |
is specified by a <literal>basetype</literal> parameter rather than being |
| 337 |
written next to the aggregate name. Note that this syntax allows |
| 338 |
only one input parameter. To define a zero-argument aggregate function |
| 339 |
with this syntax, specify the <literal>basetype</literal> as |
| 340 |
<literal>"ANY"</literal> (not <literal>*</literal>). |
| 341 |
Ordered-set aggregates cannot be defined with the old syntax. |
| 342 |
</para> |
| 343 |
</listitem> |
| 344 |
</varlistentry> |
| 345 |
|
| 346 |
<varlistentry> |
| 347 |
<term><replaceable class="parameter">sfunc</replaceable></term> |
| 348 |
<listitem> |
| 349 |
<para> |
| 350 |
The name of the state transition function to be called for each |
| 351 |
input row. For a normal <replaceable class="parameter">N</replaceable>-argument |
| 352 |
aggregate function, the <replaceable class="parameter">sfunc</replaceable> |
| 353 |
must take <replaceable class="parameter">N</replaceable>+1 arguments, |
| 354 |
the first being of type <replaceable |
| 355 |
class="parameter">state_data_type</replaceable> and the rest |
| 356 |
matching the declared input data type(s) of the aggregate. |
| 357 |
The function must return a value of type <replaceable |
| 358 |
class="parameter">state_data_type</replaceable>. This function |
| 359 |
takes the current state value and the current input data value(s), |
| 360 |
and returns the next state value. |
| 361 |
</para> |
| 362 |
|
| 363 |
<para> |
| 364 |
For ordered-set (including hypothetical-set) aggregates, the state |
| 365 |
transition function receives only the current state value and the |
| 366 |
aggregated arguments, not the direct arguments. Otherwise it is the |
| 367 |
same. |
| 368 |
</para> |
| 369 |
</listitem> |
| 370 |
</varlistentry> |
| 371 |
|
| 372 |
<varlistentry> |
| 373 |
<term><replaceable class="parameter">state_data_type</replaceable></term> |
| 374 |
<listitem> |
| 375 |
<para> |
| 376 |
The data type for the aggregate's state value. |
| 377 |
</para> |
| 378 |
</listitem> |
| 379 |
</varlistentry> |
| 380 |
|
| 381 |
<varlistentry> |
| 382 |
<term><replaceable class="parameter">state_data_size</replaceable></term> |
| 383 |
<listitem> |
| 384 |
<para> |
| 385 |
The approximate average size (in bytes) of the aggregate's state value. |
| 386 |
If this parameter is omitted or is zero, a default estimate is used |
| 387 |
based on the <replaceable>state_data_type</replaceable>. |
| 388 |
The planner uses this value to estimate the memory required for a |
| 389 |
grouped aggregate query. |
| 390 |
</para> |
| 391 |
</listitem> |
| 392 |
</varlistentry> |
| 393 |
|
| 394 |
<varlistentry> |
| 395 |
<term><replaceable class="parameter">ffunc</replaceable></term> |
| 396 |
<listitem> |
| 397 |
<para> |
| 398 |
The name of the final function called to compute the aggregate's |
| 399 |
result after all input rows have been traversed. |
| 400 |
For a normal aggregate, this function |
| 401 |
must take a single argument of type <replaceable |
| 402 |
class="parameter">state_data_type</replaceable>. The return |
| 403 |
data type of the aggregate is defined as the return type of this |
| 404 |
function. If <replaceable class="parameter">ffunc</replaceable> |
| 405 |
is not specified, then the ending state value is used as the |
| 406 |
aggregate's result, and the return type is <replaceable |
| 407 |
class="parameter">state_data_type</replaceable>. |
| 408 |
</para> |
| 409 |
|
| 410 |
<para> |
| 411 |
For ordered-set (including hypothetical-set) aggregates, the |
| 412 |
final function receives not only the final state value, |
| 413 |
but also the values of all the direct arguments. |
| 414 |
</para> |
| 415 |
|
| 416 |
<para> |
| 417 |
If <literal>FINALFUNC_EXTRA</literal> is specified, then in addition to the |
| 418 |
final state value and any direct arguments, the final function |
| 419 |
receives extra NULL values corresponding to the aggregate's regular |
| 420 |
(aggregated) arguments. This is mainly useful to allow correct |
| 421 |
resolution of the aggregate result type when a polymorphic aggregate |
| 422 |
is being defined. |
| 423 |
</para> |
| 424 |
</listitem> |
| 425 |
</varlistentry> |
| 426 |
|
| 427 |
<varlistentry> |
| 428 |
<term><literal>FINALFUNC_MODIFY</literal> = { <literal>READ_ONLY</literal> | <literal>SHAREABLE</literal> | <literal>READ_WRITE</literal> }</term> |
| 429 |
<listitem> |
| 430 |
<para> |
| 431 |
This option specifies whether the final function is a pure function |
| 432 |
that does not modify its arguments. <literal>READ_ONLY</literal> indicates |
| 433 |
it does not; the other two values indicate that it may change the |
| 434 |
transition state value. See <xref linkend="sql-createaggregate-notes"/> |
| 435 |
below for more detail. The |
| 436 |
default is <literal>READ_ONLY</literal>, except for ordered-set aggregates, |
| 437 |
for which the default is <literal>READ_WRITE</literal>. |
| 438 |
</para> |
| 439 |
</listitem> |
| 440 |
</varlistentry> |
| 441 |
|
| 442 |
<varlistentry> |
| 443 |
<term><replaceable class="parameter">combinefunc</replaceable></term> |
| 444 |
<listitem> |
| 445 |
<para> |
| 446 |
The <replaceable class="parameter">combinefunc</replaceable> function |
| 447 |
may optionally be specified to allow the aggregate function to support |
| 448 |
partial aggregation. If provided, |
| 449 |
the <replaceable class="parameter">combinefunc</replaceable> must |
| 450 |
combine two <replaceable class="parameter">state_data_type</replaceable> |
| 451 |
values, each containing the result of aggregation over some subset of |
| 452 |
the input values, to produce a |
| 453 |
new <replaceable class="parameter">state_data_type</replaceable> that |
| 454 |
represents the result of aggregating over both sets of inputs. This |
| 455 |
function can be thought of as |
| 456 |
an <replaceable class="parameter">sfunc</replaceable>, where instead of |
| 457 |
acting upon an individual input row and adding it to the running |
| 458 |
aggregate state, it adds another aggregate state to the running state. |
| 459 |
</para> |
| 460 |
|
| 461 |
<para> |
| 462 |
The <replaceable class="parameter">combinefunc</replaceable> must be |
| 463 |
declared as taking two arguments of |
| 464 |
the <replaceable class="parameter">state_data_type</replaceable> and |
| 465 |
returning a value of |
| 466 |
the <replaceable class="parameter">state_data_type</replaceable>. |
| 467 |
Optionally this function may be <quote>strict</quote>. In this case the |
| 468 |
function will not be called when either of the input states are null; |
| 469 |
the other state will be taken as the correct result. |
| 470 |
</para> |
| 471 |
|
| 472 |
<para> |
| 473 |
For aggregate functions |
| 474 |
whose <replaceable class="parameter">state_data_type</replaceable> |
| 475 |
is <type>internal</type>, |
| 476 |
the <replaceable class="parameter">combinefunc</replaceable> must not |
| 477 |
be strict. In this case |
| 478 |
the <replaceable class="parameter">combinefunc</replaceable> must |
| 479 |
ensure that null states are handled correctly and that the state being |
| 480 |
returned is properly stored in the aggregate memory context. |
| 481 |
</para> |
| 482 |
</listitem> |
| 483 |
</varlistentry> |
| 484 |
|
| 485 |
<varlistentry> |
| 486 |
<term><replaceable class="parameter">serialfunc</replaceable></term> |
| 487 |
<listitem> |
| 488 |
<para> |
| 489 |
An aggregate function |
| 490 |
whose <replaceable class="parameter">state_data_type</replaceable> |
| 491 |
is <type>internal</type> can participate in parallel aggregation only if it |
| 492 |
has a <replaceable class="parameter">serialfunc</replaceable> function, |
| 493 |
which must serialize the aggregate state into a <type>bytea</type> value for |
| 494 |
transmission to another process. This function must take a single |
| 495 |
argument of type <type>internal</type> and return type <type>bytea</type>. A |
| 496 |
corresponding <replaceable class="parameter">deserialfunc</replaceable> |
| 497 |
is also required. |
| 498 |
</para> |
| 499 |
</listitem> |
| 500 |
</varlistentry> |
| 501 |
|
| 502 |
<varlistentry> |
| 503 |
<term><replaceable class="parameter">deserialfunc</replaceable></term> |
| 504 |
<listitem> |
| 505 |
<para> |
| 506 |
Deserialize a previously serialized aggregate state back into |
| 507 |
<replaceable class="parameter">state_data_type</replaceable>. This |
| 508 |
function must take two arguments of types <type>bytea</type> |
| 509 |
and <type>internal</type>, and produce a result of type <type>internal</type>. |
| 510 |
(Note: the second, <type>internal</type> argument is unused, but is required |
| 511 |
for type safety reasons.) |
| 512 |
</para> |
| 513 |
</listitem> |
| 514 |
</varlistentry> |
| 515 |
|
| 516 |
<varlistentry> |
| 517 |
<term><replaceable class="parameter">initial_condition</replaceable></term> |
| 518 |
<listitem> |
| 519 |
<para> |
| 520 |
The initial setting for the state value. This must be a string |
| 521 |
constant in the form accepted for the data type <replaceable |
| 522 |
class="parameter">state_data_type</replaceable>. If not |
| 523 |
specified, the state value starts out null. |
| 524 |
</para> |
| 525 |
</listitem> |
| 526 |
</varlistentry> |
| 527 |
|
| 528 |
<varlistentry> |
| 529 |
<term><replaceable class="parameter">msfunc</replaceable></term> |
| 530 |
<listitem> |
| 531 |
<para> |
| 532 |
The name of the forward state transition function to be called for each |
| 533 |
input row in moving-aggregate mode. This is exactly like the regular |
| 534 |
transition function, except that its first argument and result are of |
| 535 |
type <replaceable>mstate_data_type</replaceable>, which might be different |
| 536 |
from <replaceable>state_data_type</replaceable>. |
| 537 |
</para> |
| 538 |
</listitem> |
| 539 |
</varlistentry> |
| 540 |
|
| 541 |
<varlistentry> |
| 542 |
<term><replaceable class="parameter">minvfunc</replaceable></term> |
| 543 |
<listitem> |
| 544 |
<para> |
| 545 |
The name of the inverse state transition function to be used in |
| 546 |
moving-aggregate mode. This function has the same argument and |
| 547 |
result types as <replaceable>msfunc</replaceable>, but it is used to remove |
| 548 |
a value from the current aggregate state, rather than add a value to |
| 549 |
it. The inverse transition function must have the same strictness |
| 550 |
attribute as the forward state transition function. |
| 551 |
</para> |
| 552 |
</listitem> |
| 553 |
</varlistentry> |
| 554 |
|
| 555 |
<varlistentry> |
| 556 |
<term><replaceable class="parameter">mstate_data_type</replaceable></term> |
| 557 |
<listitem> |
| 558 |
<para> |
| 559 |
The data type for the aggregate's state value, when using |
| 560 |
moving-aggregate mode. |
| 561 |
</para> |
| 562 |
</listitem> |
| 563 |
</varlistentry> |
| 564 |
|
| 565 |
<varlistentry> |
| 566 |
<term><replaceable class="parameter">mstate_data_size</replaceable></term> |
| 567 |
<listitem> |
| 568 |
<para> |
| 569 |
The approximate average size (in bytes) of the aggregate's state |
| 570 |
value, when using moving-aggregate mode. This works the same as |
| 571 |
<replaceable>state_data_size</replaceable>. |
| 572 |
</para> |
| 573 |
</listitem> |
| 574 |
</varlistentry> |
| 575 |
|
| 576 |
<varlistentry> |
| 577 |
<term><replaceable class="parameter">mffunc</replaceable></term> |
| 578 |
<listitem> |
| 579 |
<para> |
| 580 |
The name of the final function called to compute the aggregate's |
| 581 |
result after all input rows have been traversed, when using |
| 582 |
moving-aggregate mode. This works the same as <replaceable>ffunc</replaceable>, |
| 583 |
except that its first argument's type |
| 584 |
is <replaceable>mstate_data_type</replaceable> and extra dummy arguments are |
| 585 |
specified by writing <literal>MFINALFUNC_EXTRA</literal>. |
| 586 |
The aggregate result type determined by <replaceable>mffunc</replaceable> |
| 587 |
or <replaceable>mstate_data_type</replaceable> must match that determined by the |
| 588 |
aggregate's regular implementation. |
| 589 |
</para> |
| 590 |
</listitem> |
| 591 |
</varlistentry> |
| 592 |
|
| 593 |
<varlistentry> |
| 594 |
<term><literal>MFINALFUNC_MODIFY</literal> = { <literal>READ_ONLY</literal> | <literal>SHAREABLE</literal> | <literal>READ_WRITE</literal> }</term> |
| 595 |
<listitem> |
| 596 |
<para> |
| 597 |
This option is like <literal>FINALFUNC_MODIFY</literal>, but it describes |
| 598 |
the behavior of the moving-aggregate final function. |
| 599 |
</para> |
| 600 |
</listitem> |
| 601 |
</varlistentry> |
| 602 |
|
| 603 |
<varlistentry> |
| 604 |
<term><replaceable class="parameter">minitial_condition</replaceable></term> |
| 605 |
<listitem> |
| 606 |
<para> |
| 607 |
The initial setting for the state value, when using moving-aggregate |
| 608 |
mode. This works the same as <replaceable>initial_condition</replaceable>. |
| 609 |
</para> |
| 610 |
</listitem> |
| 611 |
</varlistentry> |
| 612 |
|
| 613 |
<varlistentry> |
| 614 |
<term><replaceable class="parameter">sort_operator</replaceable></term> |
| 615 |
<listitem> |
| 616 |
<para> |
| 617 |
The associated sort operator for a <function>MIN</function>- or |
| 618 |
<function>MAX</function>-like aggregate. |
| 619 |
This is just an operator name (possibly schema-qualified). |
| 620 |
The operator is assumed to have the same input data types as |
| 621 |
the aggregate (which must be a single-argument normal aggregate). |
| 622 |
</para> |
| 623 |
</listitem> |
| 624 |
</varlistentry> |
| 625 |
|
| 626 |
<varlistentry> |
| 627 |
<term><literal>PARALLEL =</literal> { <literal>SAFE</literal> | <literal>RESTRICTED</literal> | <literal>UNSAFE</literal> }</term> |
| 628 |
<listitem> |
| 629 |
<para> |
| 630 |
The meanings of <literal>PARALLEL SAFE</literal>, <literal>PARALLEL |
| 631 |
RESTRICTED</literal>, and <literal>PARALLEL UNSAFE</literal> are the same as |
| 632 |
in <link linkend="sql-createfunction"><command>CREATE FUNCTION</command></link>. An aggregate will not be |
| 633 |
considered for parallelization if it is marked <literal>PARALLEL |
| 634 |
UNSAFE</literal> (which is the default!) or <literal>PARALLEL RESTRICTED</literal>. |
| 635 |
Note that the parallel-safety markings of the aggregate's support |
| 636 |
functions are not consulted by the planner, only the marking of the |
| 637 |
aggregate itself. |
| 638 |
</para> |
| 639 |
</listitem> |
| 640 |
</varlistentry> |
| 641 |
|
| 642 |
<varlistentry> |
| 643 |
<term><literal>HYPOTHETICAL</literal></term> |
| 644 |
<listitem> |
| 645 |
<para> |
| 646 |
For ordered-set aggregates only, this flag specifies that the aggregate |
| 647 |
arguments are to be processed according to the requirements for |
| 648 |
hypothetical-set aggregates: that is, the last few direct arguments must |
| 649 |
match the data types of the aggregated (<literal>WITHIN GROUP</literal>) |
| 650 |
arguments. The <literal>HYPOTHETICAL</literal> flag has no effect on |
| 651 |
run-time behavior, only on parse-time resolution of the data types and |
| 652 |
collations of the aggregate's arguments. |
| 653 |
</para> |
| 654 |
</listitem> |
| 655 |
</varlistentry> |
| 656 |
</variablelist> |
| 657 |
|
| 658 |
<para> |
| 659 |
The parameters of <command>CREATE AGGREGATE</command> can be |
| 660 |
written in any order, not just the order illustrated above. |
| 661 |
</para> |
| 662 |
</refsect1> |
| 663 |
|
| 664 |
<refsect1 id="sql-createaggregate-notes" xreflabel="Notes"> |
| 665 |
<title>Notes</title> |
| 666 |
|
| 667 |
<para> |
| 668 |
In parameters that specify support function names, you can write |
| 669 |
a schema name if needed, for example <literal>SFUNC = public.sum</literal>. |
| 670 |
Do not write argument types there, however — the argument types |
| 671 |
of the support functions are determined from other parameters. |
| 672 |
</para> |
| 673 |
|
| 674 |
<para> |
| 675 |
Ordinarily, PostgreSQL functions are expected to be true functions that |
| 676 |
do not modify their input values. However, an aggregate transition |
| 677 |
function, <emphasis>when used in the context of an aggregate</emphasis>, |
| 678 |
is allowed to cheat and modify its transition-state argument in place. |
| 679 |
This can provide substantial performance benefits compared to making |
| 680 |
a fresh copy of the transition state each time. |
| 681 |
</para> |
| 682 |
|
| 683 |
<para> |
| 684 |
Likewise, while an aggregate final function is normally expected not to |
| 685 |
modify its input values, sometimes it is impractical to avoid modifying |
| 686 |
the transition-state argument. Such behavior must be declared using |
| 687 |
the <literal>FINALFUNC_MODIFY</literal> parameter. |
| 688 |
The <literal>READ_WRITE</literal> |
| 689 |
value indicates that the final function modifies the transition state in |
| 690 |
unspecified ways. This value prevents use of the aggregate as a window |
| 691 |
function, and it also prevents merging of transition states for aggregate |
| 692 |
calls that share the same input values and transition functions. |
| 693 |
The <literal>SHAREABLE</literal> value indicates that the transition function |
| 694 |
cannot be applied after the final function, but multiple final-function |
| 695 |
calls can be performed on the ending transition state value. This value |
| 696 |
prevents use of the aggregate as a window function, but it allows merging |
| 697 |
of transition states. (That is, the optimization of interest here is not |
| 698 |
applying the same final function repeatedly, but applying different final |
| 699 |
functions to the same ending transition state value. This is allowed as |
| 700 |
long as none of the final functions are marked <literal>READ_WRITE</literal>.) |
| 701 |
</para> |
| 702 |
|
| 703 |
<para> |
| 704 |
If an aggregate supports moving-aggregate mode, it will improve |
| 705 |
calculation efficiency when the aggregate is used as a window function |
| 706 |
for a window with moving frame start (that is, a frame start mode other |
| 707 |
than <literal>UNBOUNDED PRECEDING</literal>). Conceptually, the forward |
| 708 |
transition function adds input values to the aggregate's state when |
| 709 |
they enter the window frame from the bottom, and the inverse transition |
| 710 |
function removes them again when they leave the frame at the top. So, |
| 711 |
when values are removed, they are always removed in the same order they |
| 712 |
were added. Whenever the inverse transition function is invoked, it will |
| 713 |
thus receive the earliest added but not yet removed argument value(s). |
| 714 |
The inverse transition function can assume that at least one row will |
| 715 |
remain in the current state after it removes the oldest row. (When this |
| 716 |
would not be the case, the window function mechanism simply starts a |
| 717 |
fresh aggregation, rather than using the inverse transition function.) |
| 718 |
</para> |
| 719 |
|
| 720 |
<para> |
| 721 |
The forward transition function for moving-aggregate mode is not |
| 722 |
allowed to return NULL as the new state value. If the inverse |
| 723 |
transition function returns NULL, this is taken as an indication that |
| 724 |
the inverse function cannot reverse the state calculation for this |
| 725 |
particular input, and so the aggregate calculation will be redone from |
| 726 |
scratch for the current frame starting position. This convention |
| 727 |
allows moving-aggregate mode to be used in situations where there are |
| 728 |
some infrequent cases that are impractical to reverse out of the |
| 729 |
running state value. |
| 730 |
</para> |
| 731 |
|
| 732 |
<para> |
| 733 |
If no moving-aggregate implementation is supplied, |
| 734 |
the aggregate can still be used with moving frames, |
| 735 |
but <productname>PostgreSQL</productname> will recompute the whole |
| 736 |
aggregation whenever the start of the frame moves. |
| 737 |
Note that whether or not the aggregate supports moving-aggregate |
| 738 |
mode, <productname>PostgreSQL</productname> can handle a moving frame |
| 739 |
end without recalculation; this is done by continuing to add new values |
| 740 |
to the aggregate's state. This is why use of an aggregate as a window |
| 741 |
function requires that the final function be read-only: it must |
| 742 |
not damage the aggregate's state value, so that the aggregation can be |
| 743 |
continued even after an aggregate result value has been obtained for |
| 744 |
one set of frame boundaries. |
| 745 |
</para> |
| 746 |
|
| 747 |
<para> |
| 748 |
The syntax for ordered-set aggregates allows <literal>VARIADIC</literal> |
| 749 |
to be specified for both the last direct parameter and the last |
| 750 |
aggregated (<literal>WITHIN GROUP</literal>) parameter. However, the |
| 751 |
current implementation restricts use of <literal>VARIADIC</literal> |
| 752 |
in two ways. First, ordered-set aggregates can only use |
| 753 |
<literal>VARIADIC "any"</literal>, not other variadic array types. |
| 754 |
Second, if the last direct parameter is <literal>VARIADIC "any"</literal>, |
| 755 |
then there can be only one aggregated parameter and it must also |
| 756 |
be <literal>VARIADIC "any"</literal>. (In the representation used in the |
| 757 |
system catalogs, these two parameters are merged into a single |
| 758 |
<literal>VARIADIC "any"</literal> item, since <structname>pg_proc</structname> cannot |
| 759 |
represent functions with more than one <literal>VARIADIC</literal> parameter.) |
| 760 |
If the aggregate is a hypothetical-set aggregate, the direct arguments |
| 761 |
that match the <literal>VARIADIC "any"</literal> parameter are the hypothetical |
| 762 |
ones; any preceding parameters represent additional direct arguments |
| 763 |
that are not constrained to match the aggregated arguments. |
| 764 |
</para> |
| 765 |
|
| 766 |
<para> |
| 767 |
Currently, ordered-set aggregates do not need to support |
| 768 |
moving-aggregate mode, since they cannot be used as window functions. |
| 769 |
</para> |
| 770 |
|
| 771 |
<para> |
| 772 |
Partial (including parallel) aggregation is currently not supported for |
| 773 |
ordered-set aggregates. Also, it will never be used for aggregate calls |
| 774 |
that include <literal>DISTINCT</literal> or <literal>ORDER BY</literal> clauses, since |
| 775 |
those semantics cannot be supported during partial aggregation. |
| 776 |
</para> |
| 777 |
</refsect1> |
| 778 |
|
| 779 |
<refsect1> |
| 780 |
<title>Examples</title> |
| 781 |
|
| 782 |
<para> |
| 783 |
See <xref linkend="xaggr"/>. |
| 784 |
</para> |
| 785 |
</refsect1> |
| 786 |
|
| 787 |
<refsect1> |
| 788 |
<title>Compatibility</title> |
| 789 |
|
| 790 |
<para> |
| 791 |
<command>CREATE AGGREGATE</command> is a |
| 792 |
<productname>PostgreSQL</productname> language extension. The SQL |
| 793 |
standard does not provide for user-defined aggregate functions. |
| 794 |
</para> |
| 795 |
</refsect1> |
| 796 |
|
| 797 |
<refsect1> |
| 798 |
<title>See Also</title> |
| 799 |
|
| 800 |
<simplelist type="inline"> |
| 801 |
<member><xref linkend="sql-alteraggregate"/></member> |
| 802 |
<member><xref linkend="sql-dropaggregate"/></member> |
| 803 |
</simplelist> |
| 804 |
</refsect1> |
| 805 |
</refentry> |