create_aggregate.sgml

<refentry id="SQL-CREATEAGGREGATE">
 <refmeta>
  <refentrytitle>
   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>1998-09-09</date>
  </refsynopsisdivinfo>
  <synopsis>
CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> [ AS ]
    ( BASETYPE    = <replaceable class="PARAMETER">data_type</replaceable>
    [ , SFUNC1    = <replaceable class="PARAMETER">sfunc1</replaceable>
      , STYPE1    = <replaceable class="PARAMETER">sfunc1_return_type</replaceable> ]
    [ , SFUNC2    = <replaceable class="PARAMETER">sfunc2</replaceable>
      , STYPE2    = <replaceable class="PARAMETER">sfunc2_return_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">data_type</replaceable></term>
      <listitem>
       <para>
	The fundamental data type on which this aggregate function operates.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">sfunc1</replaceable></term>
      <listitem>
       <para>
	The state transition function
	to be called for every non-NULL field from the source column.
	It takes a variable of
	type <replaceable class="PARAMETER">sfunc1_return_type</replaceable> as
	the first argument and that field as the
	second argument.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">sfunc1_return_type</replaceable></term>
      <listitem>
       <para>
	The return type of the first transition function.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">sfunc2</replaceable></term>
      <listitem>
       <para>
	The state transition function
	to be called for every non-NULL field from the source column.
	It takes a variable
	of type <replaceable class="PARAMETER">sfunc2_return_type</replaceable>
	as the only argument and returns a variable of the same type.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">sfunc2_return_type</replaceable></term>
      <listitem>
       <para>
	The return type of the second transition function.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">ffunc</replaceable></term>
      <listitem>
       <para>
	The final function
	called after traversing all input fields. This function must
	take two arguments of types
	<replaceable class="PARAMETER">sfunc1_return_type</replaceable>
	and
	<replaceable class="PARAMETER">sfunc2_return_type</replaceable>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">initial_condition1</replaceable></term>
      <listitem>
       <para>
	The initial value for the first transition function argument.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">initial_condition2</replaceable></term>
      <listitem>
       <para>
	The initial value for the second transition function argument.
       </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 can require up to three functions, two
   state transition functions, 
   <replaceable class="PARAMETER">sfunc1</replaceable>
   and <replaceable class="PARAMETER">sfunc2</replaceable>:
   <programlisting>
<replaceable class="PARAMETER">sfunc1</replaceable>( internal-state1, next-data_item ) ---> next-internal-state1 <replaceable class="PARAMETER">sfunc2</replaceable>( internal-state2 ) ---> next-internal-state2
   </programlisting>
   and a final calculation function,
   <replaceable class="PARAMETER">ffunc</replaceable>:
   <programlisting>
<replaceable class="PARAMETER">ffunc</replaceable>(internal-state1, internal-state2) ---> aggregate-value
   </programlisting>
  </para>
  <para>
   <productname>Postgres</productname> creates up to two temporary variables
   (referred to here as <replaceable class="PARAMETER">temp1</replaceable>
   and <replaceable class="PARAMETER">temp2</replaceable>)
   to hold intermediate results used as arguments to the transition functions.
  </para>
  <para>
   These transition functions are required to have the following properties:
   <itemizedlist>
    <listitem>
     <para>
      The  arguments  to 
      <replaceable class="PARAMETER">sfunc1</replaceable>
      must be
      <replaceable class="PARAMETER">temp1</replaceable>
      of type
      <replaceable class="PARAMETER">sfunc1_return_type</replaceable>
      and
      <replaceable class="PARAMETER">column_value</replaceable>
      of type <replaceable class="PARAMETER">data_type</replaceable>.
      The return value must  be of type
      <replaceable class="PARAMETER">sfunc1_return_type</replaceable>
      and will be used as the first argument in the next call to 
      <replaceable class="PARAMETER">sfunc1</replaceable>.
     </para>
    </listitem>
    
    <listitem>
     <para>
      The  argument and return value of 
      <replaceable class="PARAMETER">sfunc2</replaceable>
      must be
      <replaceable class="PARAMETER">temp2</replaceable>
      of type
      <replaceable class="PARAMETER">sfunc2_return_type</replaceable>.
     </para>
    </listitem>
    <listitem>     
     <para>
      The  arguments  to  the  final-calculation-function
      must  be
      <replaceable class="PARAMETER">temp1</replaceable>
      and
      <replaceable class="PARAMETER">temp2</replaceable>
      and its return value must
      be a <productname>Postgres</productname>
      base type (not necessarily
      <replaceable class="PARAMETER">data_type</replaceable> 
      which had been specified for BASETYPE).
     </para>
    </listitem>
    <listitem>
     <para>	
      FINALFUNC should be specified
      if and only if both state-transition functions  are
      specified. 
     </para></listitem>
   </itemizedlist>
  </para>  
  
  <para>	
   An aggregate function may also  require  one or two initial conditions,
 one for
   each transition function.  These are specified and  stored
   in the database as fields of type <type>text</type>.
  </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>
    It  is possible to specify aggregate functions
    that have varying combinations of state  and  final  functions. 
    For example, the <function>count</function> aggregate requires SFUNC2
    (an incrementing function) but not  SFUNC1  or  FINALFUNC,
    whereas  the  <function>sum</function> aggregate requires SFUNC1 (an addition
    function) but not SFUNC2 or FINALFUNC  and  the  <function>avg</function>
    aggregate  requires 
    both  of the above state functions as
    well as a FINALFUNC (a division function) to  produce  its
    answer.   In any case, at least one state function must be
    defined, and any SFUNC2 must have  a  corresponding  INITCOND2.
   </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>
   on aggregate functions 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:
-->