granicus.if.org Git - postgresql/blob - doc/src/sgml/ref/create_aggregate.sgml

   1 <!--
   2 doc/src/sgml/ref/create_aggregate.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATEAGGREGATE">
   7  <refmeta>
   8   <refentrytitle>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 <phrase>or the old syntax</phrase>
  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
 167   <para>
 168    To be able to create an aggregate function, you must
 169    have <literal>USAGE</literal> privilege on the argument types, the state
 170    type, and the return type, as well as <literal>EXECUTE</literal> privilege
 171    on the transition and final functions.
 172   </para>
 173  </refsect1>
 174
 175  <refsect1>
 176   <title>Parameters</title>
 177
 178   <variablelist>
 179    <varlistentry>
 180     <term><replaceable class="PARAMETER">name</replaceable></term>
 181     <listitem>
 182      <para>
 183       The name (optionally schema-qualified) of the aggregate function
 184       to create.
 185      </para>
 186     </listitem>
 187    </varlistentry>
 188
 189    <varlistentry>
 190     <term><replaceable class="PARAMETER">input_data_type</replaceable></term>
 191     <listitem>
 192      <para>
 193       An input data type on which this aggregate function operates.
 194       To create a zero-argument aggregate function, write <literal>*</>
 195       in place of the list of input data types.  (An example of such an
 196       aggregate is <function>count(*)</function>.)
 197      </para>
 198     </listitem>
 199    </varlistentry>
 200
 201    <varlistentry>
 202     <term><replaceable class="PARAMETER">base_type</replaceable></term>
 203     <listitem>
 204      <para>
 205       In the old syntax for <command>CREATE AGGREGATE</>, the input data type
 206       is specified by a <literal>basetype</> parameter rather than being
 207       written next to the aggregate name.  Note that this syntax allows
 208       only one input parameter.  To define a zero-argument aggregate function,
 209       specify the <literal>basetype</> as
 210       <literal>"ANY"</> (not <literal>*</>).
 211      </para>
 212     </listitem>
 213    </varlistentry>
 214
 215    <varlistentry>
 216     <term><replaceable class="PARAMETER">sfunc</replaceable></term>
 217     <listitem>
 218      <para>
 219       The name of the state transition function to be called for each
 220       input row.  For an <replaceable class="PARAMETER">N</>-argument
 221       aggregate function, the <replaceable class="PARAMETER">sfunc</>
 222       must take <replaceable class="PARAMETER">N</>+1 arguments,
 223       the first being of type <replaceable
 224       class="PARAMETER">state_data_type</replaceable> and the rest
 225       matching the declared input data type(s) of the aggregate.
 226       The function must return a value of type <replaceable
 227       class="PARAMETER">state_data_type</replaceable>.  This function
 228       takes the current state value and the current input data value(s),
 229       and returns the next state value.
 230      </para>
 231     </listitem>
 232    </varlistentry>
 233
 234    <varlistentry>
 235     <term><replaceable class="PARAMETER">state_data_type</replaceable></term>
 236     <listitem>
 237      <para>
 238       The data type for the aggregate's state value.
 239      </para>
 240     </listitem>
 241    </varlistentry>
 242
 243    <varlistentry>
 244     <term><replaceable class="PARAMETER">ffunc</replaceable></term>
 245     <listitem>
 246      <para>
 247       The name of the final function called to compute the aggregate's
 248       result after all input rows have been traversed.  The function
 249       must take a single argument of type <replaceable
 250       class="PARAMETER">state_data_type</replaceable>.  The return
 251       data type of the aggregate is defined as the return type of this
 252       function.  If <replaceable class="PARAMETER">ffunc</replaceable>
 253       is not specified, then the ending state value is used as the
 254       aggregate's result, and the return type is <replaceable
 255       class="PARAMETER">state_data_type</replaceable>.
 256      </para>
 257     </listitem>
 258    </varlistentry>
 259
 260    <varlistentry>
 261     <term><replaceable class="PARAMETER">initial_condition</replaceable></term>
 262     <listitem>
 263      <para>
 264       The initial setting for the state value.  This must be a string
 265       constant in the form accepted for the data type <replaceable
 266       class="PARAMETER">state_data_type</replaceable>.  If not
 267       specified, the state value starts out null.
 268      </para>
 269     </listitem>
 270    </varlistentry>
 271
 272    <varlistentry>
 273     <term><replaceable class="PARAMETER">sort_operator</replaceable></term>
 274     <listitem>
 275      <para>
 276       The associated sort operator for a <function>MIN</>- or
 277       <function>MAX</>-like aggregate.
 278       This is just an operator name (possibly schema-qualified).
 279       The operator is assumed to have the same input data types as
 280       the aggregate (which must be a single-argument aggregate).
 281      </para>
 282     </listitem>
 283    </varlistentry>
 284   </variablelist>
 285
 286   <para>
 287    The parameters of <command>CREATE AGGREGATE</command> can be
 288    written in any order, not just the order illustrated above.
 289   </para>
 290  </refsect1>
 291
 292  <refsect1>
 293   <title>Examples</title>
 294
 295   <para>
 296    See <xref linkend="xaggr">.
 297   </para>
 298  </refsect1>
 299
 300  <refsect1>
 301   <title>Compatibility</title>
 302
 303   <para>
 304    <command>CREATE AGGREGATE</command> is a
 305    <productname>PostgreSQL</productname> language extension.  The SQL
 306    standard does not provide for user-defined aggregate functions.
 307   </para>
 308  </refsect1>
 309
 310  <refsect1>
 311   <title>See Also</title>
 312
 313   <simplelist type="inline">
 314    <member><xref linkend="sql-alteraggregate"></member>
 315    <member><xref linkend="sql-dropaggregate"></member>
 316   </simplelist>
 317  </refsect1>
 318 </refentry>