summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-createaggregate.html
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src/sgml/html/sql-createaggregate.html')
-rw-r--r--doc/src/sgml/html/sql-createaggregate.html510
1 files changed, 510 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createaggregate.html b/doc/src/sgml/html/sql-createaggregate.html
new file mode 100644
index 0000000..335f192
--- /dev/null
+++ b/doc/src/sgml/html/sql-createaggregate.html
@@ -0,0 +1,510 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE AGGREGATE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-create-access-method.html" title="CREATE ACCESS METHOD" /><link rel="next" href="sql-createcast.html" title="CREATE CAST" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE AGGREGATE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-create-access-method.html" title="CREATE ACCESS METHOD">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createcast.html" title="CREATE CAST">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-CREATEAGGREGATE"><div class="titlepage"></div><a id="id-1.9.3.57.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE AGGREGATE</span></h2><p>CREATE AGGREGATE — define a new aggregate function</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
+CREATE [ OR REPLACE ] AGGREGATE <em class="replaceable"><code>name</code></em> ( [ <em class="replaceable"><code>argmode</code></em> ] [ <em class="replaceable"><code>argname</code></em> ] <em class="replaceable"><code>arg_data_type</code></em> [ , ... ] ) (
+ SFUNC = <em class="replaceable"><code>sfunc</code></em>,
+ STYPE = <em class="replaceable"><code>state_data_type</code></em>
+ [ , SSPACE = <em class="replaceable"><code>state_data_size</code></em> ]
+ [ , FINALFUNC = <em class="replaceable"><code>ffunc</code></em> ]
+ [ , FINALFUNC_EXTRA ]
+ [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
+ [ , COMBINEFUNC = <em class="replaceable"><code>combinefunc</code></em> ]
+ [ , SERIALFUNC = <em class="replaceable"><code>serialfunc</code></em> ]
+ [ , DESERIALFUNC = <em class="replaceable"><code>deserialfunc</code></em> ]
+ [ , INITCOND = <em class="replaceable"><code>initial_condition</code></em> ]
+ [ , MSFUNC = <em class="replaceable"><code>msfunc</code></em> ]
+ [ , MINVFUNC = <em class="replaceable"><code>minvfunc</code></em> ]
+ [ , MSTYPE = <em class="replaceable"><code>mstate_data_type</code></em> ]
+ [ , MSSPACE = <em class="replaceable"><code>mstate_data_size</code></em> ]
+ [ , MFINALFUNC = <em class="replaceable"><code>mffunc</code></em> ]
+ [ , MFINALFUNC_EXTRA ]
+ [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
+ [ , MINITCOND = <em class="replaceable"><code>minitial_condition</code></em> ]
+ [ , SORTOP = <em class="replaceable"><code>sort_operator</code></em> ]
+ [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
+)
+
+CREATE [ OR REPLACE ] AGGREGATE <em class="replaceable"><code>name</code></em> ( [ [ <em class="replaceable"><code>argmode</code></em> ] [ <em class="replaceable"><code>argname</code></em> ] <em class="replaceable"><code>arg_data_type</code></em> [ , ... ] ]
+ ORDER BY [ <em class="replaceable"><code>argmode</code></em> ] [ <em class="replaceable"><code>argname</code></em> ] <em class="replaceable"><code>arg_data_type</code></em> [ , ... ] ) (
+ SFUNC = <em class="replaceable"><code>sfunc</code></em>,
+ STYPE = <em class="replaceable"><code>state_data_type</code></em>
+ [ , SSPACE = <em class="replaceable"><code>state_data_size</code></em> ]
+ [ , FINALFUNC = <em class="replaceable"><code>ffunc</code></em> ]
+ [ , FINALFUNC_EXTRA ]
+ [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
+ [ , INITCOND = <em class="replaceable"><code>initial_condition</code></em> ]
+ [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
+ [ , HYPOTHETICAL ]
+)
+
+<span class="phrase">or the old syntax</span>
+
+CREATE [ OR REPLACE ] AGGREGATE <em class="replaceable"><code>name</code></em> (
+ BASETYPE = <em class="replaceable"><code>base_type</code></em>,
+ SFUNC = <em class="replaceable"><code>sfunc</code></em>,
+ STYPE = <em class="replaceable"><code>state_data_type</code></em>
+ [ , SSPACE = <em class="replaceable"><code>state_data_size</code></em> ]
+ [ , FINALFUNC = <em class="replaceable"><code>ffunc</code></em> ]
+ [ , FINALFUNC_EXTRA ]
+ [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
+ [ , COMBINEFUNC = <em class="replaceable"><code>combinefunc</code></em> ]
+ [ , SERIALFUNC = <em class="replaceable"><code>serialfunc</code></em> ]
+ [ , DESERIALFUNC = <em class="replaceable"><code>deserialfunc</code></em> ]
+ [ , INITCOND = <em class="replaceable"><code>initial_condition</code></em> ]
+ [ , MSFUNC = <em class="replaceable"><code>msfunc</code></em> ]
+ [ , MINVFUNC = <em class="replaceable"><code>minvfunc</code></em> ]
+ [ , MSTYPE = <em class="replaceable"><code>mstate_data_type</code></em> ]
+ [ , MSSPACE = <em class="replaceable"><code>mstate_data_size</code></em> ]
+ [ , MFINALFUNC = <em class="replaceable"><code>mffunc</code></em> ]
+ [ , MFINALFUNC_EXTRA ]
+ [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
+ [ , MINITCOND = <em class="replaceable"><code>minitial_condition</code></em> ]
+ [ , SORTOP = <em class="replaceable"><code>sort_operator</code></em> ]
+)
+</pre></div><div class="refsect1" id="id-1.9.3.57.5"><h2>Description</h2><p>
+ <code class="command">CREATE AGGREGATE</code> defines a new aggregate function.
+ <code class="command">CREATE OR REPLACE AGGREGATE</code> will either define a new
+ aggregate function or replace an existing definition. Some basic and
+ commonly-used aggregate functions are included with the distribution; they
+ are documented in <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a>. If one defines new
+ types or needs an aggregate function not already provided, then
+ <code class="command">CREATE AGGREGATE</code> can be used to provide the desired
+ features.
+ </p><p>
+ When replacing an existing definition, the argument types, result type,
+ and number of direct arguments may not be changed. Also, the new definition
+ must be of the same kind (ordinary aggregate, ordered-set aggregate, or
+ hypothetical-set aggregate) as the old one.
+ </p><p>
+ If a schema name is given (for example, <code class="literal">CREATE AGGREGATE
+ myschema.myagg ...</code>) then the aggregate function is created in the
+ specified schema. Otherwise it is created in the current schema.
+ </p><p>
+ An aggregate function is identified by its name and input data type(s).
+ Two aggregates in the same schema can have the same name if they operate on
+ different input types. The
+ name and input data type(s) of an aggregate must also be distinct from
+ the name and input data type(s) of every ordinary function in the same
+ schema.
+ This behavior is identical to overloading of ordinary function names
+ (see <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>).
+ </p><p>
+ A simple aggregate function is made from one or two ordinary
+ functions:
+ a state transition function
+ <em class="replaceable"><code>sfunc</code></em>,
+ and an optional final calculation function
+ <em class="replaceable"><code>ffunc</code></em>.
+ These are used as follows:
+</p><pre class="programlisting">
+<em class="replaceable"><code>sfunc</code></em>( internal-state, next-data-values ) ---&gt; next-internal-state
+<em class="replaceable"><code>ffunc</code></em>( internal-state ) ---&gt; aggregate-value
+</pre><p>
+ </p><p>
+ <span class="productname">PostgreSQL</span> creates a temporary variable
+ of data type <em class="replaceable"><code>stype</code></em>
+ to hold the current internal state of the aggregate. At each input row,
+ the aggregate argument value(s) are calculated and
+ the state transition function is invoked with the current state value
+ and the new argument value(s) to calculate a new
+ internal state value. After all the rows have been processed,
+ the final function is invoked once to calculate the aggregate's return
+ value. If there is no final function then the ending state value
+ is returned as-is.
+ </p><p>
+ An aggregate function can provide an initial condition,
+ that is, an initial value for the internal state value.
+ This is specified and stored in the database as a value of type
+ <code class="type">text</code>, but it must be a valid external representation
+ of a constant of the state value data type. If it is not supplied
+ then the state value starts out null.
+ </p><p>
+ If the state transition function is declared <span class="quote">“<span class="quote">strict</span>”</span>,
+ then it cannot be called with null inputs. With such a transition
+ function, aggregate execution behaves as follows. Rows with any null input
+ values are ignored (the function is not called and the previous state value
+ is retained). If the initial state value is null, then at the first row
+ with all-nonnull input values, the first argument value replaces the state
+ value, and the transition function is invoked at each subsequent row with
+ all-nonnull input values.
+ This is handy for implementing aggregates like <code class="function">max</code>.
+ Note that this behavior is only available when
+ <em class="replaceable"><code>state_data_type</code></em>
+ is the same as the first
+ <em class="replaceable"><code>arg_data_type</code></em>.
+ When these types are different, you must supply a nonnull initial
+ condition or use a nonstrict transition function.
+ </p><p>
+ If the state transition function is not strict, then it will be called
+ unconditionally at each input row, and must deal with null inputs
+ and null state values for itself. This allows the aggregate
+ author to have full control over the aggregate's handling of null values.
+ </p><p>
+ If the final function is declared <span class="quote">“<span class="quote">strict</span>”</span>, then it will not
+ be called when the ending state value is null; instead a null result
+ will be returned automatically. (Of course this is just the normal
+ behavior of strict functions.) In any case the final function has
+ the option of returning a null value. For example, the final function for
+ <code class="function">avg</code> returns null when it sees there were zero
+ input rows.
+ </p><p>
+ Sometimes it is useful to declare the final function as taking not just
+ the state value, but extra parameters corresponding to the aggregate's
+ input values. The main reason for doing this is if the final function
+ is polymorphic and the state value's data type would be inadequate to
+ pin down the result type. These extra parameters are always passed as
+ NULL (and so the final function must not be strict when
+ the <code class="literal">FINALFUNC_EXTRA</code> option is used), but nonetheless they
+ are valid parameters. The final function could for example make use
+ of <code class="function">get_fn_expr_argtype</code> to identify the actual argument type
+ in the current call.
+ </p><p>
+ An aggregate can optionally support <em class="firstterm">moving-aggregate mode</em>,
+ as described in <a class="xref" href="xaggr.html#XAGGR-MOVING-AGGREGATES" title="38.12.1. Moving-Aggregate Mode">Section 38.12.1</a>. This requires
+ specifying the <code class="literal">MSFUNC</code>, <code class="literal">MINVFUNC</code>,
+ and <code class="literal">MSTYPE</code> parameters, and optionally
+ the <code class="literal">MSSPACE</code>, <code class="literal">MFINALFUNC</code>,
+ <code class="literal">MFINALFUNC_EXTRA</code>, <code class="literal">MFINALFUNC_MODIFY</code>,
+ and <code class="literal">MINITCOND</code> parameters. Except for <code class="literal">MINVFUNC</code>,
+ these parameters work like the corresponding simple-aggregate parameters
+ without <code class="literal">M</code>; they define a separate implementation of the
+ aggregate that includes an inverse transition function.
+ </p><p>
+ The syntax with <code class="literal">ORDER BY</code> in the parameter list creates
+ a special type of aggregate called an <em class="firstterm">ordered-set
+ aggregate</em>; or if <code class="literal">HYPOTHETICAL</code> is specified, then
+ a <em class="firstterm">hypothetical-set aggregate</em> is created. These
+ aggregates operate over groups of sorted values in order-dependent ways,
+ so that specification of an input sort order is an essential part of a
+ call. Also, they can have <em class="firstterm">direct</em> arguments, which are
+ arguments that are evaluated only once per aggregation rather than once
+ per input row. Hypothetical-set aggregates are a subclass of ordered-set
+ aggregates in which some of the direct arguments are required to match,
+ in number and data types, the aggregated argument columns. This allows
+ the values of those direct arguments to be added to the collection of
+ aggregate-input rows as an additional <span class="quote">“<span class="quote">hypothetical</span>”</span> row.
+ </p><p>
+ An aggregate can optionally support <em class="firstterm">partial aggregation</em>,
+ as described in <a class="xref" href="xaggr.html#XAGGR-PARTIAL-AGGREGATES" title="38.12.4. Partial Aggregation">Section 38.12.4</a>.
+ This requires specifying the <code class="literal">COMBINEFUNC</code> parameter.
+ If the <em class="replaceable"><code>state_data_type</code></em>
+ is <code class="type">internal</code>, it's usually also appropriate to provide the
+ <code class="literal">SERIALFUNC</code> and <code class="literal">DESERIALFUNC</code> parameters so that
+ parallel aggregation is possible. Note that the aggregate must also be
+ marked <code class="literal">PARALLEL SAFE</code> to enable parallel aggregation.
+ </p><p>
+ Aggregates that behave like <code class="function">MIN</code> or <code class="function">MAX</code> can
+ sometimes be optimized by looking into an index instead of scanning every
+ input row. If this aggregate can be so optimized, indicate it by
+ specifying a <em class="firstterm">sort operator</em>. The basic requirement is that
+ the aggregate must yield the first element in the sort ordering induced by
+ the operator; in other words:
+</p><pre class="programlisting">
+SELECT agg(col) FROM tab;
+</pre><p>
+ must be equivalent to:
+</p><pre class="programlisting">
+SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
+</pre><p>
+ Further assumptions are that the aggregate ignores null inputs, and that
+ it delivers a null result if and only if there were no non-null inputs.
+ Ordinarily, a data type's <code class="literal">&lt;</code> operator is the proper sort
+ operator for <code class="function">MIN</code>, and <code class="literal">&gt;</code> is the proper sort
+ operator for <code class="function">MAX</code>. Note that the optimization will never
+ actually take effect unless the specified operator is the <span class="quote">“<span class="quote">less
+ than</span>”</span> or <span class="quote">“<span class="quote">greater than</span>”</span> strategy member of a B-tree
+ index operator class.
+ </p><p>
+ To be able to create an aggregate function, you must
+ have <code class="literal">USAGE</code> privilege on the argument types, the state
+ type(s), and the return type, as well as <code class="literal">EXECUTE</code>
+ privilege on the supporting functions.
+ </p></div><div class="refsect1" id="id-1.9.3.57.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
+ The name (optionally schema-qualified) of the aggregate function
+ to create.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>argmode</code></em></span></dt><dd><p>
+ The mode of an argument: <code class="literal">IN</code> or <code class="literal">VARIADIC</code>.
+ (Aggregate functions do not support <code class="literal">OUT</code> arguments.)
+ If omitted, the default is <code class="literal">IN</code>. Only the last argument
+ can be marked <code class="literal">VARIADIC</code>.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>argname</code></em></span></dt><dd><p>
+ The name of an argument. This is currently only useful for
+ documentation purposes. If omitted, the argument has no name.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>arg_data_type</code></em></span></dt><dd><p>
+ An input data type on which this aggregate function operates.
+ To create a zero-argument aggregate function, write <code class="literal">*</code>
+ in place of the list of argument specifications. (An example of such an
+ aggregate is <code class="function">count(*)</code>.)
+ </p></dd><dt><span class="term"><em class="replaceable"><code>base_type</code></em></span></dt><dd><p>
+ In the old syntax for <code class="command">CREATE AGGREGATE</code>, the input data type
+ is specified by a <code class="literal">basetype</code> parameter rather than being
+ written next to the aggregate name. Note that this syntax allows
+ only one input parameter. To define a zero-argument aggregate function
+ with this syntax, specify the <code class="literal">basetype</code> as
+ <code class="literal">"ANY"</code> (not <code class="literal">*</code>).
+ Ordered-set aggregates cannot be defined with the old syntax.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>sfunc</code></em></span></dt><dd><p>
+ The name of the state transition function to be called for each
+ input row. For a normal <em class="replaceable"><code>N</code></em>-argument
+ aggregate function, the <em class="replaceable"><code>sfunc</code></em>
+ must take <em class="replaceable"><code>N</code></em>+1 arguments,
+ the first being of type <em class="replaceable"><code>state_data_type</code></em> and the rest
+ matching the declared input data type(s) of the aggregate.
+ The function must return a value of type <em class="replaceable"><code>state_data_type</code></em>. This function
+ takes the current state value and the current input data value(s),
+ and returns the next state value.
+ </p><p>
+ For ordered-set (including hypothetical-set) aggregates, the state
+ transition function receives only the current state value and the
+ aggregated arguments, not the direct arguments. Otherwise it is the
+ same.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>state_data_type</code></em></span></dt><dd><p>
+ The data type for the aggregate's state value.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>state_data_size</code></em></span></dt><dd><p>
+ The approximate average size (in bytes) of the aggregate's state value.
+ If this parameter is omitted or is zero, a default estimate is used
+ based on the <em class="replaceable"><code>state_data_type</code></em>.
+ The planner uses this value to estimate the memory required for a
+ grouped aggregate query.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>ffunc</code></em></span></dt><dd><p>
+ The name of the final function called to compute the aggregate's
+ result after all input rows have been traversed.
+ For a normal aggregate, this function
+ must take a single argument of type <em class="replaceable"><code>state_data_type</code></em>. The return
+ data type of the aggregate is defined as the return type of this
+ function. If <em class="replaceable"><code>ffunc</code></em>
+ is not specified, then the ending state value is used as the
+ aggregate's result, and the return type is <em class="replaceable"><code>state_data_type</code></em>.
+ </p><p>
+ For ordered-set (including hypothetical-set) aggregates, the
+ final function receives not only the final state value,
+ but also the values of all the direct arguments.
+ </p><p>
+ If <code class="literal">FINALFUNC_EXTRA</code> is specified, 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.
+ </p></dd><dt><span class="term"><code class="literal">FINALFUNC_MODIFY</code> = { <code class="literal">READ_ONLY</code> | <code class="literal">SHAREABLE</code> | <code class="literal">READ_WRITE</code> }</span></dt><dd><p>
+ This option specifies whether the final function is a pure function
+ that does not modify its arguments. <code class="literal">READ_ONLY</code> indicates
+ it does not; the other two values indicate that it may change the
+ transition state value. See <a class="xref" href="sql-createaggregate.html#SQL-CREATEAGGREGATE-NOTES" title="Notes">Notes</a>
+ below for more detail. The
+ default is <code class="literal">READ_ONLY</code>, except for ordered-set aggregates,
+ for which the default is <code class="literal">READ_WRITE</code>.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>combinefunc</code></em></span></dt><dd><p>
+ The <em class="replaceable"><code>combinefunc</code></em> function
+ may optionally be specified to allow the aggregate function to support
+ partial aggregation. If provided,
+ the <em class="replaceable"><code>combinefunc</code></em> must
+ combine two <em class="replaceable"><code>state_data_type</code></em>
+ values, each containing the result of aggregation over some subset of
+ the input values, to produce a
+ new <em class="replaceable"><code>state_data_type</code></em> that
+ represents the result of aggregating over both sets of inputs. This
+ function can be thought of as
+ an <em class="replaceable"><code>sfunc</code></em>, where instead of
+ acting upon an individual input row and adding it to the running
+ aggregate state, it adds another aggregate state to the running state.
+ </p><p>
+ The <em class="replaceable"><code>combinefunc</code></em> must be
+ declared as taking two arguments of
+ the <em class="replaceable"><code>state_data_type</code></em> and
+ returning a value of
+ the <em class="replaceable"><code>state_data_type</code></em>.
+ Optionally this function may be <span class="quote">“<span class="quote">strict</span>”</span>. In this case the
+ function will not be called when either of the input states are null;
+ the other state will be taken as the correct result.
+ </p><p>
+ For aggregate functions
+ whose <em class="replaceable"><code>state_data_type</code></em>
+ is <code class="type">internal</code>,
+ the <em class="replaceable"><code>combinefunc</code></em> must not
+ be strict. In this case
+ the <em class="replaceable"><code>combinefunc</code></em> must
+ ensure that null states are handled correctly and that the state being
+ returned is properly stored in the aggregate memory context.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>serialfunc</code></em></span></dt><dd><p>
+ An aggregate function
+ whose <em class="replaceable"><code>state_data_type</code></em>
+ is <code class="type">internal</code> can participate in parallel aggregation only if it
+ has a <em class="replaceable"><code>serialfunc</code></em> function,
+ which must serialize the aggregate state into a <code class="type">bytea</code> value for
+ transmission to another process. This function must take a single
+ argument of type <code class="type">internal</code> and return type <code class="type">bytea</code>. A
+ corresponding <em class="replaceable"><code>deserialfunc</code></em>
+ is also required.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>deserialfunc</code></em></span></dt><dd><p>
+ Deserialize a previously serialized aggregate state back into
+ <em class="replaceable"><code>state_data_type</code></em>. This
+ function must take two arguments of types <code class="type">bytea</code>
+ and <code class="type">internal</code>, and produce a result of type <code class="type">internal</code>.
+ (Note: the second, <code class="type">internal</code> argument is unused, but is required
+ for type safety reasons.)
+ </p></dd><dt><span class="term"><em class="replaceable"><code>initial_condition</code></em></span></dt><dd><p>
+ The initial setting for the state value. This must be a string
+ constant in the form accepted for the data type <em class="replaceable"><code>state_data_type</code></em>. If not
+ specified, the state value starts out null.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>msfunc</code></em></span></dt><dd><p>
+ 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 <em class="replaceable"><code>mstate_data_type</code></em>, which might be different
+ from <em class="replaceable"><code>state_data_type</code></em>.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>minvfunc</code></em></span></dt><dd><p>
+ 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 <em class="replaceable"><code>msfunc</code></em>, 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.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>mstate_data_type</code></em></span></dt><dd><p>
+ The data type for the aggregate's state value, when using
+ moving-aggregate mode.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>mstate_data_size</code></em></span></dt><dd><p>
+ The approximate average size (in bytes) of the aggregate's state
+ value, when using moving-aggregate mode. This works the same as
+ <em class="replaceable"><code>state_data_size</code></em>.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>mffunc</code></em></span></dt><dd><p>
+ 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. This works the same as <em class="replaceable"><code>ffunc</code></em>,
+ except that its first argument's type
+ is <em class="replaceable"><code>mstate_data_type</code></em> and extra dummy arguments are
+ specified by writing <code class="literal">MFINALFUNC_EXTRA</code>.
+ The aggregate result type determined by <em class="replaceable"><code>mffunc</code></em>
+ or <em class="replaceable"><code>mstate_data_type</code></em> must match that determined by the
+ aggregate's regular implementation.
+ </p></dd><dt><span class="term"><code class="literal">MFINALFUNC_MODIFY</code> = { <code class="literal">READ_ONLY</code> | <code class="literal">SHAREABLE</code> | <code class="literal">READ_WRITE</code> }</span></dt><dd><p>
+ This option is like <code class="literal">FINALFUNC_MODIFY</code>, but it describes
+ the behavior of the moving-aggregate final function.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>minitial_condition</code></em></span></dt><dd><p>
+ The initial setting for the state value, when using moving-aggregate
+ mode. This works the same as <em class="replaceable"><code>initial_condition</code></em>.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>sort_operator</code></em></span></dt><dd><p>
+ The associated sort operator for a <code class="function">MIN</code>- or
+ <code class="function">MAX</code>-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).
+ </p></dd><dt><span class="term"><code class="literal">PARALLEL =</code> { <code class="literal">SAFE</code> | <code class="literal">RESTRICTED</code> | <code class="literal">UNSAFE</code> }</span></dt><dd><p>
+ The meanings of <code class="literal">PARALLEL SAFE</code>, <code class="literal">PARALLEL
+ RESTRICTED</code>, and <code class="literal">PARALLEL UNSAFE</code> are the same as
+ in <a class="link" href="sql-createfunction.html" title="CREATE FUNCTION"><code class="command">CREATE FUNCTION</code></a>. An aggregate will not be
+ considered for parallelization if it is marked <code class="literal">PARALLEL
+ UNSAFE</code> (which is the default!) or <code class="literal">PARALLEL RESTRICTED</code>.
+ 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.
+ </p></dd><dt><span class="term"><code class="literal">HYPOTHETICAL</code></span></dt><dd><p>
+ 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 (<code class="literal">WITHIN GROUP</code>)
+ arguments. The <code class="literal">HYPOTHETICAL</code> flag has no effect on
+ run-time behavior, only on parse-time resolution of the data types and
+ collations of the aggregate's arguments.
+ </p></dd></dl></div><p>
+ The parameters of <code class="command">CREATE AGGREGATE</code> can be
+ written in any order, not just the order illustrated above.
+ </p></div><div class="refsect1" id="SQL-CREATEAGGREGATE-NOTES"><h2>Notes</h2><p>
+ In parameters that specify support function names, you can write
+ a schema name if needed, for example <code class="literal">SFUNC = public.sum</code>.
+ Do not write argument types there, however — the argument types
+ of the support functions are determined from other parameters.
+ </p><p>
+ Ordinarily, PostgreSQL functions are expected to be true functions that
+ do not modify their input values. However, an aggregate transition
+ function, <span class="emphasis"><em>when used in the context of an aggregate</em></span>,
+ is allowed to cheat and modify its transition-state argument in place.
+ This can provide substantial performance benefits compared to making
+ a fresh copy of the transition state each time.
+ </p><p>
+ Likewise, while an aggregate final function is normally expected not to
+ modify its input values, sometimes it is impractical to avoid modifying
+ the transition-state argument. Such behavior must be declared using
+ the <code class="literal">FINALFUNC_MODIFY</code> parameter.
+ The <code class="literal">READ_WRITE</code>
+ value indicates that the final function modifies the transition state in
+ unspecified ways. This value prevents use of the aggregate as a window
+ function, and it also prevents merging of transition states for aggregate
+ calls that share the same input values and transition functions.
+ The <code class="literal">SHAREABLE</code> value indicates that the transition function
+ cannot be applied after the final function, but multiple final-function
+ calls can be performed on the ending transition state value. This value
+ prevents use of the aggregate as a window function, but it allows merging
+ of transition states. (That is, the optimization of interest here is not
+ applying the same final function repeatedly, but applying different final
+ functions to the same ending transition state value. This is allowed as
+ long as none of the final functions are marked <code class="literal">READ_WRITE</code>.)
+ </p><p>
+ If an aggregate supports moving-aggregate mode, it will improve
+ calculation efficiency when the aggregate is used as a window function
+ for a window with moving frame start (that is, a frame start mode other
+ than <code class="literal">UNBOUNDED PRECEDING</code>). Conceptually, the forward
+ transition function adds input values to the aggregate's state when
+ they enter the window frame from the bottom, and the inverse transition
+ function removes them again when they leave the frame at the top. So,
+ when values are removed, they are always removed in the same order they
+ were added. Whenever the inverse transition function is invoked, it will
+ thus receive the earliest added but not yet removed argument value(s).
+ The inverse transition function can assume that at least one row will
+ remain in the current state after it removes the oldest row. (When this
+ would not be the case, the window function mechanism simply starts a
+ fresh aggregation, rather than using the inverse transition function.)
+ </p><p>
+ The forward transition function for moving-aggregate mode is not
+ allowed to return NULL as the new state value. If the inverse
+ transition function returns NULL, this is taken as an indication that
+ the inverse function cannot reverse the state calculation for this
+ particular input, and so the aggregate calculation will be redone from
+ scratch for the current frame starting position. This convention
+ allows moving-aggregate mode to be used in situations where there are
+ some infrequent cases that are impractical to reverse out of the
+ running state value.
+ </p><p>
+ If no moving-aggregate implementation is supplied,
+ the aggregate can still be used with moving frames,
+ but <span class="productname">PostgreSQL</span> will recompute the whole
+ aggregation whenever the start of the frame moves.
+ Note that whether or not the aggregate supports moving-aggregate
+ mode, <span class="productname">PostgreSQL</span> can handle a moving frame
+ end without recalculation; this is done by continuing to add new values
+ to the aggregate's state. This is why use of an aggregate as a window
+ function requires that the final function be read-only: it must
+ not damage the aggregate's state value, so that the aggregation can be
+ continued even after an aggregate result value has been obtained for
+ one set of frame boundaries.
+ </p><p>
+ The syntax for ordered-set aggregates allows <code class="literal">VARIADIC</code>
+ to be specified for both the last direct parameter and the last
+ aggregated (<code class="literal">WITHIN GROUP</code>) parameter. However, the
+ current implementation restricts use of <code class="literal">VARIADIC</code>
+ in two ways. First, ordered-set aggregates can only use
+ <code class="literal">VARIADIC "any"</code>, not other variadic array types.
+ Second, if the last direct parameter is <code class="literal">VARIADIC "any"</code>,
+ then there can be only one aggregated parameter and it must also
+ be <code class="literal">VARIADIC "any"</code>. (In the representation used in the
+ system catalogs, these two parameters are merged into a single
+ <code class="literal">VARIADIC "any"</code> item, since <code class="structname">pg_proc</code> cannot
+ represent functions with more than one <code class="literal">VARIADIC</code> parameter.)
+ If the aggregate is a hypothetical-set aggregate, the direct arguments
+ that match the <code class="literal">VARIADIC "any"</code> parameter are the hypothetical
+ ones; any preceding parameters represent additional direct arguments
+ that are not constrained to match the aggregated arguments.
+ </p><p>
+ Currently, ordered-set aggregates do not need to support
+ moving-aggregate mode, since they cannot be used as window functions.
+ </p><p>
+ Partial (including parallel) aggregation is currently not supported for
+ ordered-set aggregates. Also, it will never be used for aggregate calls
+ that include <code class="literal">DISTINCT</code> or <code class="literal">ORDER BY</code> clauses, since
+ those semantics cannot be supported during partial aggregation.
+ </p></div><div class="refsect1" id="id-1.9.3.57.8"><h2>Examples</h2><p>
+ See <a class="xref" href="xaggr.html" title="38.12. User-Defined Aggregates">Section 38.12</a>.
+ </p></div><div class="refsect1" id="id-1.9.3.57.9"><h2>Compatibility</h2><p>
+ <code class="command">CREATE AGGREGATE</code> is a
+ <span class="productname">PostgreSQL</span> language extension. The SQL
+ standard does not provide for user-defined aggregate functions.
+ </p></div><div class="refsect1" id="id-1.9.3.57.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alteraggregate.html" title="ALTER AGGREGATE"><span class="refentrytitle">ALTER AGGREGATE</span></a>, <a class="xref" href="sql-dropaggregate.html" title="DROP AGGREGATE"><span class="refentrytitle">DROP AGGREGATE</span></a></span></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-create-access-method.html" title="CREATE ACCESS METHOD">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createcast.html" title="CREATE CAST">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE ACCESS METHOD </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE CAST</td></tr></table></div></body></html> \ No newline at end of file