path: root/doc/src/sgml/ref/create_aggregate.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_aggregate.sgml,v 1.9 2000/03/31 14:57:05 tgl Exp $
Postgres documentation
-->

<refentry id="SQL-CREATEAGGREGATE">
 <refmeta>
  <refentrytitle id="sql-createaggregate-title">
   CREATE AGGREGATE
  </refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>
   CREATE AGGREGATE
  </refname>
  <refpurpose>
   Defines a new aggregate function
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>1999-07-20</date>
  </refsynopsisdivinfo>
  <synopsis>
CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> ( BASETYPE = <replaceable class="PARAMETER">input_data_type</replaceable>
    [ , SFUNC1 = <replaceable class="PARAMETER">sfunc1</replaceable>, STYPE1 = <replaceable class="PARAMETER">state1_type</replaceable> ]
    [ , SFUNC2 = <replaceable class="PARAMETER">sfunc2</replaceable>, STYPE2 = <replaceable class="PARAMETER">state2_type</replaceable> ]
    [ , FINALFUNC = <replaceable class="PARAMETER">ffunc</replaceable> ]
    [ , INITCOND1 = <replaceable class="PARAMETER">initial_condition1</replaceable> ]
    [ , INITCOND2 = <replaceable class="PARAMETER">initial_condition2</replaceable> ] )
  </synopsis>

  <refsect2 id="R2-SQL-CREATEAGGREGATE-1">
   <refsect2info>
    <date>1998-09-09</date>
   </refsect2info>
   <title>
    Inputs
   </title>
   <para>
    <variablelist>
     <varlistentry>
      <term><replaceable class="PARAMETER">name</replaceable></term>
      <listitem>
       <para>
	The name of an aggregate function to create.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">input_data_type</replaceable></term>
      <listitem>
       <para>
	The input data type on which this aggregate function operates.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">sfunc1</replaceable></term>
      <listitem>
       <para>
	A state transition function
	to be called for every non-NULL input data value.
	This must be a function of two arguments, the first being of
	type <replaceable class="PARAMETER">state1_type</replaceable>
	and the second of
	type <replaceable class="PARAMETER">input_data_type</replaceable>.
	The function must return a value of
	type <replaceable class="PARAMETER">state1_type</replaceable>.
	This function takes the current state value 1 and the current
	input data item, and returns the next state value 1.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">state1_type</replaceable></term>
      <listitem>
       <para>
	The data type for the first state value of the aggregate.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">sfunc2</replaceable></term>
      <listitem>
       <para>
	A state transition function
	to be called for every non-NULL input data value.
	This must be a function of one argument of
	type <replaceable class="PARAMETER">state2_type</replaceable>,
	returning a value of the same type.
	This function takes the current state value 2 and
	returns the next state value 2.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">state2_type</replaceable></term>
      <listitem>
       <para>
	The data type for the second state value of the aggregate.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">ffunc</replaceable></term>
      <listitem>
       <para>
	The final function called to compute the aggregate's result
	after all input data has been traversed.
	If both state values are used, the final function must
	take two arguments of types
	<replaceable class="PARAMETER">state1_type</replaceable>
	and
	<replaceable class="PARAMETER">state2_type</replaceable>.
	If only one state value is used, the final function must
	take a single argument of that state value's type.
	The output datatype of the aggregate is defined as the return
	type of this function.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">initial_condition1</replaceable></term>
      <listitem>
       <para>
	The initial value for state value 1.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">initial_condition2</replaceable></term>
      <listitem>
       <para>
	The initial value for state value 2.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

  <refsect2 id="R2-SQL-CREATEAGGREGATE-2">
   <refsect2info>
    <date>1998-09-09</date>
   </refsect2info>
   <title>
    Outputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term><computeroutput>
CREATE
       </computeroutput></term>
      <listitem>
       <para>
	Message returned if the command completes successfully.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
 </refsynopsisdiv>

 <refsect1 id="R1-SQL-CREATEAGGREGATE-1">
  <refsect1info>
   <date>1998-09-09</date>
  </refsect1info>
  <title>
   Description
  </title>
  <para>
   <command>CREATE AGGREGATE</command> 
   allows a user or programmer to extend <productname>Postgres</productname>
   functionality by defining new aggregate functions. Some aggregate functions
   for base types such as <function>min(int4)</function>
   and <function>avg(float8)</function> are already provided in the base
   distribution. If one defines new types or needs an aggregate function not
   already provided then <command>CREATE AGGREGATE</command>
   can be used to provide the desired features.
  </para>
  <para>
   An  aggregate  function is identified by its name and input data type.
   Two aggregates can have the same name if they operate on different
   input types.  To avoid confusion, do not make an ordinary function
   of the same name and input data type as an aggregate.
  </para>
  <para>
   An  aggregate  function is made from between one and three ordinary
   functions:
   two state transition functions, 
   <replaceable class="PARAMETER">sfunc1</replaceable>
   and <replaceable class="PARAMETER">sfunc2</replaceable>,
   and a final calculation function,
   <replaceable class="PARAMETER">ffunc</replaceable>.
   These are used as follows:
   <programlisting>
<replaceable class="PARAMETER">sfunc1</replaceable>( internal-state1, next-data-item ) ---> next-internal-state1
<replaceable class="PARAMETER">sfunc2</replaceable>( internal-state2 ) ---> next-internal-state2
<replaceable class="PARAMETER">ffunc</replaceable>(internal-state1, internal-state2) ---> aggregate-value
   </programlisting>
  </para>
  <para>
   <productname>Postgres</productname> creates one or two temporary variables
   (of data types <replaceable class="PARAMETER">stype1</replaceable> and/or
   <replaceable class="PARAMETER">stype2</replaceable>) to hold the
   current internal states of the aggregate.  At each input data item,
   the state transition function(s) are invoked to calculate new values
   for the internal state values.  After all the data has been processed,
   the final function is invoked once to calculate the aggregate's output
   value.
  </para>
  <para>
   <replaceable class="PARAMETER">ffunc</replaceable> must be specified if
   both transition functions are specified.  If only one transition function
   is used, then <replaceable class="PARAMETER">ffunc</replaceable> is
   optional.  The default behavior when
   <replaceable class="PARAMETER">ffunc</replaceable> is not provided is
   to return the ending value of the internal state value being used
   (and, therefore, the aggregate's output type is the same as that
   state value's type).
  </para>  
  
  <para>	
   An aggregate function may also provide one or two initial conditions,
   that is, initial values for the internal state values being used.
   These are specified and  stored in the database as fields of type
   <type>text</type>, but they must be valid external representations
   of constants of the state value datatypes.  If
   <replaceable class="PARAMETER">sfunc1</replaceable> is specified 
   without an <replaceable class="PARAMETER">initcond1</replaceable> value,
   then the system does not call
   <replaceable class="PARAMETER">sfunc1</replaceable> 
   at the first input item; instead, the internal state value 1 is
   initialized with the first input value, and
   <replaceable class="PARAMETER">sfunc1</replaceable> is called beginning 
   at the second input item.  This is useful for aggregates like MIN and
   MAX.  Note that an aggregate using this feature will return NULL when
   called with no input values.  There is no comparable provision for
   state value 2; if <replaceable class="PARAMETER">sfunc2</replaceable> is
   specified then an <replaceable class="PARAMETER">initcond2</replaceable> is
   required.
  </para>
  
  <refsect2 id="R2-SQL-CREATEAGGREGATE-3">
   <refsect2info>
    <date>1998-09-09</date>
   </refsect2info>
   <title>
    Notes
   </title>
   <para>
    Use <command>DROP AGGREGATE</command>
    to drop aggregate functions.
   </para>

   <para>
    The parameters of <command>CREATE AGGREGATE</command> can be written
    in any order, not just the order illustrated above.
   </para>

   <para>
    It  is possible to specify aggregate functions
    that have varying combinations of state  and  final  functions. 
    For example, the <function>count</function> aggregate requires
    <replaceable class="PARAMETER">sfunc2</replaceable> 
    (an incrementing function) but not
    <replaceable class="PARAMETER">sfunc1</replaceable>  or  
    <replaceable class="PARAMETER">ffunc</replaceable>,
    whereas  the  <function>sum</function> aggregate requires
    <replaceable class="PARAMETER">sfunc1</replaceable> (an addition
    function) but not <replaceable class="PARAMETER">sfunc2</replaceable> or
    <replaceable class="PARAMETER">ffunc</replaceable>,  and  the
    <function>avg</function>
    aggregate  requires 
    both state functions as
    well as a <replaceable class="PARAMETER">ffunc</replaceable> (a division
    function) to  produce  its 
    answer.   In any case, at least one state function must be
    defined, and any <replaceable class="PARAMETER">sfunc2</replaceable> must
    have  a  corresponding
    <replaceable class="PARAMETER">initcond2</replaceable>. 
   </para>

  </refsect2>
 </refsect1>

 <refsect1 id="R1-SQL-CREATEAGGREGATE-2">
  <title>
   Usage
  </title>
  <para>
   Refer to the chapter on aggregate functions
   in the <citetitle>PostgreSQL Programmer's Guide</citetitle> for
   complete examples of usage.
  </para>
 </refsect1>

 <refsect1 id="R1-SQL-CREATEAGGREGATE-3">
  <title>
   Compatibility
  </title>

  <refsect2 id="R2-SQL-CREATEAGGREGATE-4">
   <refsect2info>
    <date>1998-09-09</date>
   </refsect2info>
   <title>
    SQL92
   </title>
   <para>
    <command>CREATE AGGREGATE</command> 
    is a <productname>Postgres</productname> language extension.
    There is no <command>CREATE AGGREGATE</command> in SQL92.
   </para>
  </refsect2>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->