Home Explore Blog CI



postgresql

5th chunk of `doc/src/sgml/ref/create_aggregate.sgml`
2063279a099fe5930b0dc8495876bc402f2f62dd783692250000000100000fa6
 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 <quote>hypothetical</quote> row.
  </para>

  <para>
   An aggregate can optionally support <firstterm>partial aggregation</firstterm>,
   as described in <xref linkend="xaggr-partial-aggregates"/>.
   This requires specifying the <literal>COMBINEFUNC</literal> parameter.
   If the <replaceable class="parameter">state_data_type</replaceable>
   is <type>internal</type>, it's usually also appropriate to provide the
   <literal>SERIALFUNC</literal> and <literal>DESERIALFUNC</literal> parameters so that
   parallel aggregation is possible.  Note that the aggregate must also be
   marked <literal>PARALLEL SAFE</literal> to enable parallel aggregation.
  </para>

  <para>
   Aggregates that behave like <function>MIN</function> or <function>MAX</function> 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 <firstterm>sort operator</firstterm>.  The basic requirement is that
   the aggregate must yield the first element in the sort ordering induced by
   the operator; in other words:
<programlisting>
SELECT agg(col) FROM tab;
</programlisting>
   must be equivalent to:
<programlisting>
SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
</programlisting>
   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 <literal>&lt;</literal> operator is the proper sort
   operator for <function>MIN</function>, and <literal>&gt;</literal> is the proper sort
   operator for <function>MAX</function>.  Note that the optimization will never
   actually take effect unless the specified operator is the <quote>less
   than</quote> or <quote>greater than</quote> strategy member of a B-tree
   index operator class.
  </para>

  <para>
   To be able to create an aggregate function, you must
   have <literal>USAGE</literal> privilege on the argument types, the state
   type(s), and the return type, as well as <literal>EXECUTE</literal>
   privilege on the supporting functions.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><replaceable class="parameter">name</replaceable></term>
    <listitem>
     <para>
      The name (optionally schema-qualified) of the aggregate function
      to create.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">argmode</replaceable></term>

    <listitem>
     <para>
      The mode of an argument: <literal>IN</literal> or <literal>VARIADIC</literal>.
      (Aggregate functions do not support <literal>OUT</literal> arguments.)
      If omitted, the default is <literal>IN</literal>.  Only the last argument
      can be marked <literal>VARIADIC</literal>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">argname</replaceable></term>

    <listitem>
     <para>
      The name of an argument.  This is currently only useful for
      documentation purposes.  If omitted, the argument has no name.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">arg_data_type</replaceable></term>
    <listitem>
     <para>
      An input data type on which this aggregate function operates.
      To create a zero-argument aggregate function, write <literal>*</literal>
      in place of the list of argument specifications.  (An example of such an
      aggregate is <function>count(*)</function>.)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">base_type</replaceable></term>
    <listitem>
     <para>
      In the old syntax for <command>CREATE AGGREGATE</command>,

Title: Partial Aggregation, Index Optimization, Privileges, and Parameter Definitions for CREATE AGGREGATE
Summary
This section details partial aggregation, which enhances performance through the COMBINEFUNC, SERIALFUNC, and DESERIALFUNC parameters, enabling parallel aggregation. It also explains how aggregates like MIN/MAX can be optimized using indexes with sort operators. Furthermore, it outlines the required privileges for creating aggregates and defines parameters for the CREATE AGGREGATE command, including argument modes, names, and data types.