diff options
Diffstat (limited to 'doc/src/sgml/ref/create_aggregate.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_aggregate.sgml | 316 |
1 files changed, 0 insertions, 316 deletions
diff --git a/doc/src/sgml/ref/create_aggregate.sgml b/doc/src/sgml/ref/create_aggregate.sgml deleted file mode 100644 index 21c547f6b67..00000000000 --- a/doc/src/sgml/ref/create_aggregate.sgml +++ /dev/null @@ -1,316 +0,0 @@ -<!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_aggregate.sgml,v 1.20 2002/05/18 15:44:47 petere Exp $ -PostgreSQL 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> - define a new aggregate function - </refpurpose> - </refnamediv> - <refsynopsisdiv> - <refsynopsisdivinfo> - <date>2000-07-16</date> - </refsynopsisdivinfo> - <synopsis> -CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> ( BASETYPE = <replaceable class="PARAMETER">input_data_type</replaceable>, - SFUNC = <replaceable class="PARAMETER">sfunc</replaceable>, STYPE = <replaceable class="PARAMETER">state_type</replaceable> - [ , FINALFUNC = <replaceable class="PARAMETER">ffunc</replaceable> ] - [ , INITCOND = <replaceable class="PARAMETER">initial_condition</replaceable> ] ) - </synopsis> - - <refsect2 id="R2-SQL-CREATEAGGREGATE-1"> - <refsect2info> - <date>2000-07-16</date> - </refsect2info> - <title> - Inputs - </title> - <para> - <variablelist> - <varlistentry> - <term><replaceable class="PARAMETER">name</replaceable></term> - <listitem> - <para> - The name (optionally schema-qualified) 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. - This can be specified as ANY for an aggregate that does not - examine its input values - (an example is <function>count(*)</function>). - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">sfunc</replaceable></term> - <listitem> - <para> - The name of the state transition function - to be called for each input data value. - This is normally a function of two arguments, the first being of - type <replaceable class="PARAMETER">state_type</replaceable> - and the second of - type <replaceable class="PARAMETER">input_data_type</replaceable>. - Alternatively, for an aggregate that does not examine its input - values, the function takes just one argument of - type <replaceable class="PARAMETER">state_type</replaceable>. - In either case the function must return a value of - type <replaceable class="PARAMETER">state_type</replaceable>. - This function takes the current state value and the current - input data item, and returns the next state value. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">state_type</replaceable></term> - <listitem> - <para> - The data type for the aggregate's state value. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">ffunc</replaceable></term> - <listitem> - <para> - The name of the final function called to compute the aggregate's - result after all input data has been traversed. The function - must take a single argument of type - <replaceable class="PARAMETER">state_type</replaceable>. - The output data type of the aggregate is defined as the return - type of this function. - If <replaceable class="PARAMETER">ffunc</replaceable> - is not specified, then the ending state value is used as the - aggregate's result, and the output type is - <replaceable class="PARAMETER">state_type</replaceable>. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="PARAMETER">initial_condition</replaceable></term> - <listitem> - <para> - The initial setting for the state value. This must be a literal - constant in the form accepted for the data type - <replaceable class="PARAMETER">state_type</replaceable>. - If not specified, the state value starts out NULL. - </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 AGGREGATE - </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>2000-07-16</date> - </refsect1info> - <title> - Description - </title> - <para> - <command>CREATE AGGREGATE</command> - allows a user or programmer to extend <productname>PostgreSQL</productname> - functionality by defining new aggregate functions. Some aggregate functions - for base types such as <function>min(integer)</function> - and <function>avg(double precision)</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> - If a schema name is given (for example, <literal>CREATE AGGREGATE - myschema.myagg ...</>) then the aggregate function is created in the - specified schema. Otherwise it is created in the current schema (the one - at the front of the search path; see <literal>CURRENT_SCHEMA()</>). - </para> - <para> - An aggregate function is identified by its name and input data type. - Two aggregates in the same schema can have the same name if they operate on - different input types. The - name and input data type of an aggregate must also be distinct from - the name and input data type(s) of every ordinary function in the same - schema. - </para> - <para> - An aggregate function is made from one or two ordinary - functions: - a state transition function - <replaceable class="PARAMETER">sfunc</replaceable>, - and an optional final calculation function - <replaceable class="PARAMETER">ffunc</replaceable>. - These are used as follows: - <programlisting> -<replaceable class="PARAMETER">sfunc</replaceable>( internal-state, next-data-item ) ---> next-internal-state -<replaceable class="PARAMETER">ffunc</replaceable>( internal-state ) ---> aggregate-value - </programlisting> - </para> - <para> - <productname>PostgreSQL</productname> creates a temporary variable - of data type <replaceable class="PARAMETER">stype</replaceable> - to hold the current internal state of the aggregate. At each input - data item, - the state transition function is invoked to calculate a new - internal state value. After all the data has been processed, - the final function is invoked once to calculate the aggregate's output - value. If there is no final function then the ending state value - is returned as-is. - </para> - - <para> - An aggregate function may provide an initial condition, - that is, an initial value for the internal state value. - This is specified and stored in the database as a field of type - <type>text</type>, but it must be a valid external representation - of a constant of the state value data type. If it is not supplied - then the state value starts out NULL. - </para> - - <para> - If the state transition function is declared <quote>strict</quote>, - then it cannot be called with NULL inputs. With such a transition - function, aggregate execution behaves as follows. NULL input values - are ignored (the function is not called and the previous state value - is retained). If the initial state value is NULL, then the first - non-NULL input value replaces the state value, and the transition - function is invoked beginning with the second non-NULL input value. - This is handy for implementing aggregates like <function>max</function>. - Note that this behavior is only available when - <replaceable class="PARAMETER">state_type</replaceable> - is the same as - <replaceable class="PARAMETER">input_data_type</replaceable>. - When these types are different, you must supply a non-NULL initial - condition or use a non-strict transition function. - </para> - - <para> - If the state transition function is not strict, then it will be called - unconditionally at each input value, and must deal with NULL inputs - and NULL transition values for itself. This allows the aggregate - author to have full control over the aggregate's handling of NULLs. - </para> - - <para> - If the final function is declared <quote>strict</quote>, then it will not - be called when the ending state value is NULL; instead a NULL result - will be output automatically. (Of course this is just the normal - behavior of strict functions.) In any case the final function has - the option of returning NULL. For example, the final function for - <function>avg</function> returns NULL when it sees there were zero - input tuples. - </para> - - <refsect2 id="R2-SQL-CREATEAGGREGATE-3"> - <refsect2info> - <date>2000-07-16</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> - - </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>PostgreSQL</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: ---> |