doc/src/sgml/ref/create_aggregate.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATEAGGREGATE">
   7  <refmeta>
   8   <refentrytitle id="sql-createaggregate-title">CREATE AGGREGATE</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>CREATE AGGREGATE</refname>
  15   <refpurpose>define a new aggregate function</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-createaggregate">
  19   <primary>CREATE AGGREGATE</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> ( <replaceable class="PARAMETER">input_data_type</replaceable> [ , ... ] ) (
  25     SFUNC = <replaceable class="PARAMETER">sfunc</replaceable>,
  26     STYPE = <replaceable class="PARAMETER">state_data_type</replaceable>
  27     [ , FINALFUNC = <replaceable class="PARAMETER">ffunc</replaceable> ]
  28     [ , INITCOND = <replaceable class="PARAMETER">initial_condition</replaceable> ]
  29     [ , SORTOP = <replaceable class="PARAMETER">sort_operator</replaceable> ]
  30 )
  31
  32 or the old syntax
  33
  34 CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> (
  35     BASETYPE = <replaceable class="PARAMETER">base_type</replaceable>,
  36     SFUNC = <replaceable class="PARAMETER">sfunc</replaceable>,
  37     STYPE = <replaceable class="PARAMETER">state_data_type</replaceable>
  38     [ , FINALFUNC = <replaceable class="PARAMETER">ffunc</replaceable> ]
  39     [ , INITCOND = <replaceable class="PARAMETER">initial_condition</replaceable> ]
  40     [ , SORTOP = <replaceable class="PARAMETER">sort_operator</replaceable> ]
  41 )
  42 </synopsis>
  43  </refsynopsisdiv>
  44
  45  <refsect1>
  46   <title>Description</title>
  47
  48   <para>
  49    <command>CREATE AGGREGATE</command> defines a new aggregate
  50    function. Some basic and commonly-used aggregate functions are
  51    included with the distribution; they are documented in <xref
  52    linkend="functions-aggregate">. If one defines new types or needs
  53    an aggregate function not already provided, then <command>CREATE
  54    AGGREGATE</command> can be used to provide the desired features.
  55   </para>
  56
  57   <para>
  58    If a schema name is given (for example, <literal>CREATE AGGREGATE
  59    myschema.myagg ...</>) then the aggregate function is created in the
  60    specified schema.  Otherwise it is created in the current schema.
  61   </para>
  62
  63   <para>
  64    An aggregate function is identified by its name and input data type(s).
  65    Two aggregates in the same schema can have the same name if they operate on
  66    different input types.  The
  67    name and input data type(s) of an aggregate must also be distinct from
  68    the name and input data type(s) of every ordinary function in the same
  69    schema.
  70   </para>
  71
  72   <para>
  73    An aggregate function is made from one or two ordinary
  74    functions:
  75    a state transition function
  76    <replaceable class="PARAMETER">sfunc</replaceable>,
  77    and an optional final calculation function
  78    <replaceable class="PARAMETER">ffunc</replaceable>.
  79    These are used as follows:
  80 <programlisting>
  81 <replaceable class="PARAMETER">sfunc</replaceable>( internal-state, next-data-values ) ---> next-internal-state
  82 <replaceable class="PARAMETER">ffunc</replaceable>( internal-state ) ---> aggregate-value
  83 </programlisting>
  84   </para>
  85
  86   <para>
  87    <productname>PostgreSQL</productname> creates a temporary variable
  88    of data type <replaceable class="PARAMETER">stype</replaceable>
  89    to hold the current internal state of the aggregate.  At each input row,
  90    the aggregate argument value(s) are calculated and
  91    the state transition function is invoked with the current state value
  92    and the new argument value(s) to calculate a new
  93    internal state value.  After all the rows have been processed,
  94    the final function is invoked once to calculate the aggregate's return
  95    value.  If there is no final function then the ending state value
  96    is returned as-is.
  97   </para>
  98
  99   <para>
 100    An aggregate function can provide an initial condition,
 101    that is, an initial value for the internal state value.
 102    This is specified and stored in the database as a value of type
 103    <type>text</type>, but it must be a valid external representation
 104    of a constant of the state value data type.  If it is not supplied
 105    then the state value starts out null.
 106   </para>
 107
 108   <para>
 109    If the state transition function is declared <quote>strict</quote>,
 110    then it cannot be called with null inputs.  With such a transition
 111    function, aggregate execution behaves as follows.  Rows with any null input
 112    values are ignored (the function is not called and the previous state value
 113    is retained).  If the initial state value is null, then at the first row
 114    with all-nonnull input values, the first argument value replaces the state
 115    value, and the transition function is invoked at subsequent rows with
 116    all-nonnull input values.
 117    This is handy for implementing aggregates like <function>max</function>.
 118    Note that this behavior is only available when
 119    <replaceable class="PARAMETER">state_data_type</replaceable>
 120    is the same as the first
 121    <replaceable class="PARAMETER">input_data_type</replaceable>.
 122    When these types are different, you must supply a nonnull initial
 123    condition or use a nonstrict transition function.
 124   </para>
 125
 126   <para>
 127    If the state transition function is not strict, then it will be called
 128    unconditionally at each input row, and must deal with null inputs
 129    and null transition values for itself.  This allows the aggregate
 130    author to have full control over the aggregate's handling of null values.
 131   </para>
 132
 133   <para>
 134    If the final function is declared <quote>strict</quote>, then it will not
 135    be called when the ending state value is null; instead a null result
 136    will be returned automatically.  (Of course this is just the normal
 137    behavior of strict functions.)  In any case the final function has
 138    the option of returning a null value.  For example, the final function for
 139    <function>avg</function> returns null when it sees there were zero
 140    input rows.
 141   </para>
 142
 143   <para>
 144    Aggregates that behave like <function>MIN</> or <function>MAX</> can
 145    sometimes be optimized by looking into an index instead of scanning every
 146    input row.  If this aggregate can be so optimized, indicate it by
 147    specifying a <firstterm>sort operator</>.  The basic requirement is that
 148    the aggregate must yield the first element in the sort ordering induced by
 149    the operator; in other words:
 150 <programlisting>
 151 SELECT agg(col) FROM tab;
 152 </programlisting>
 153    must be equivalent to:
 154 <programlisting>
 155 SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
 156 </programlisting>
 157    Further assumptions are that the aggregate ignores null inputs, and that
 158    it delivers a null result if and only if there were no non-null inputs.
 159    Ordinarily, a data type's <literal>&lt;</> operator is the proper sort
 160    operator for <function>MIN</>, and <literal>&gt;</> is the proper sort
 161    operator for <function>MAX</>.  Note that the optimization will never
 162    actually take effect unless the specified operator is the <quote>less
 163    than</quote> or <quote>greater than</quote> strategy member of a B-tree
 164    index operator class.
 165   </para>
 166  </refsect1>
 167
 168  <refsect1>
 169   <title>Parameters</title>
 170
 171   <variablelist>
 172    <varlistentry>
 173     <term><replaceable class="PARAMETER">name</replaceable></term>
 174     <listitem>
 175      <para>
 176       The name (optionally schema-qualified) of the aggregate function
 177       to create.
 178      </para>
 179     </listitem>
 180    </varlistentry>
 181
 182    <varlistentry>
 183     <term><replaceable class="PARAMETER">input_data_type</replaceable></term>
 184     <listitem>
 185      <para>
 186       An input data type on which this aggregate function operates.
 187       To create a zero-argument aggregate function, write <literal>*</>
 188       in place of the list of input data types.  (An example of such an
 189       aggregate is <function>count(*)</function>.)
 190      </para>
 191     </listitem>
 192    </varlistentry>
 193
 194    <varlistentry>
 195     <term><replaceable class="PARAMETER">base_type</replaceable></term>
 196     <listitem>
 197      <para>
 198       In the old syntax for <command>CREATE AGGREGATE</>, the input data type
 199       is specified by a <literal>basetype</> parameter rather than being
 200       written next to the aggregate name.  Note that this syntax allows
 201       only one input parameter.  To define a zero-argument aggregate function,
 202       specify the <literal>basetype</> as
 203       <literal>"ANY"</> (not <literal>*</>).
 204      </para>
 205     </listitem>
 206    </varlistentry>
 207
 208    <varlistentry>
 209     <term><replaceable class="PARAMETER">sfunc</replaceable></term>
 210     <listitem>
 211      <para>
 212       The name of the state transition function to be called for each
 213       input row.  For an <replaceable class="PARAMETER">N</>-argument
 214       aggregate function, the <replaceable class="PARAMETER">sfunc</>
 215       must take <replaceable class="PARAMETER">N</>+1 arguments,
 216       the first being of type <replaceable
 217       class="PARAMETER">state_data_type</replaceable> and the rest
 218       matching the declared input data type(s) of the aggregate.
 219       The function must return a value of type <replaceable
 220       class="PARAMETER">state_data_type</replaceable>.  This function
 221       takes the current state value and the current input data value(s),
 222       and returns the next state value.
 223      </para>
 224     </listitem>
 225    </varlistentry>
 226
 227    <varlistentry>
 228     <term><replaceable class="PARAMETER">state_data_type</replaceable></term>
 229     <listitem>
 230      <para>
 231       The data type for the aggregate's state value.
 232      </para>
 233     </listitem>
 234    </varlistentry>
 235
 236    <varlistentry>
 237     <term><replaceable class="PARAMETER">ffunc</replaceable></term>
 238     <listitem>
 239      <para>
 240       The name of the final function called to compute the aggregate's
 241       result after all input rows have been traversed.  The function
 242       must take a single argument of type <replaceable
 243       class="PARAMETER">state_data_type</replaceable>.  The return
 244       data type of the aggregate is defined as the return type of this
 245       function.  If <replaceable class="PARAMETER">ffunc</replaceable>
 246       is not specified, then the ending state value is used as the
 247       aggregate's result, and the return type is <replaceable
 248       class="PARAMETER">state_data_type</replaceable>.
 249      </para>
 250     </listitem>
 251    </varlistentry>
 252
 253    <varlistentry>
 254     <term><replaceable class="PARAMETER">initial_condition</replaceable></term>
 255     <listitem>
 256      <para>
 257       The initial setting for the state value.  This must be a string
 258       constant in the form accepted for the data type <replaceable
 259       class="PARAMETER">state_data_type</replaceable>.  If not
 260       specified, the state value starts out null.
 261      </para>
 262     </listitem>
 263    </varlistentry>
 264
 265    <varlistentry>
 266     <term><replaceable class="PARAMETER">sort_operator</replaceable></term>
 267     <listitem>
 268      <para>
 269       The associated sort operator for a <function>MIN</>- or
 270       <function>MAX</>-like aggregate.
 271       This is just an operator name (possibly schema-qualified).
 272       The operator is assumed to have the same input data types as
 273       the aggregate (which must be a single-argument aggregate).
 274      </para>
 275     </listitem>
 276    </varlistentry>
 277   </variablelist>
 278
 279   <para>
 280    The parameters of <command>CREATE AGGREGATE</command> can be
 281    written in any order, not just the order illustrated above.
 282   </para>
 283  </refsect1>
 284
 285  <refsect1>
 286   <title>Examples</title>
 287
 288   <para>
 289    See <xref linkend="xaggr">.
 290   </para>
 291  </refsect1>
 292
 293  <refsect1>
 294   <title>Compatibility</title>
 295
 296   <para>
 297    <command>CREATE AGGREGATE</command> is a
 298    <productname>PostgreSQL</productname> language extension.  The SQL
 299    standard does not provide for user-defined aggregate functions.
 300   </para>
 301  </refsect1>
 302
 303  <refsect1>
 304   <title>See Also</title>
 305
 306   <simplelist type="inline">
 307    <member><xref linkend="sql-alteraggregate" endterm="sql-alteraggregate-title"></member>
 308    <member><xref linkend="sql-dropaggregate" endterm="sql-dropaggregate-title"></member>
 309   </simplelist>
 310  </refsect1>
 311 </refentry>