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>
 <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>

  </REFSECT2>

  <REFSECT2 ID="R2-SQL-CREATEAGGREGATE-2">
   <REFSECT2INFO>
    <DATE>1998-09-09</DATE>
   </REFSECT2INFO>
   <TITLE>
    Outputs
   </TITLE>
   <PARA>

       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>CREATE</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
          Message returned if the command completes successfully.
   </VARIABLELIST>

  </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>
   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>
<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>
   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 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.
  
 </REFSECT1>
 
 <REFSECT1 ID="R1-SQL-CREATEAGGREGATE-3">
  <TITLE>
   Compatibility
  </TITLE>
  <PARA>

  <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>
   
</REFENTRY>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:t
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:
-->