diff options
Diffstat (limited to 'doc/src/sgml/ref/create_aggregate.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_aggregate.sgml | 217 |
1 files changed, 143 insertions, 74 deletions
diff --git a/doc/src/sgml/ref/create_aggregate.sgml b/doc/src/sgml/ref/create_aggregate.sgml index 9dc3ba950f..a5b04daa26 100644 --- a/doc/src/sgml/ref/create_aggregate.sgml +++ b/doc/src/sgml/ref/create_aggregate.sgml @@ -6,10 +6,6 @@ <REFMISCINFO>SQL - Language Statements</REFMISCINFO> </REFMETA> -<comment>This entry needs a lot of work, especially some -usefully complex examples. Since I don't yet understand it, I -haven't done this.</comment> - <REFNAMEDIV> <REFNAME> CREATE AGGREGATE @@ -19,32 +15,28 @@ haven't done this.</comment> </REFPURPOSE> <REFSYNOPSISDIV> <REFSYNOPSISDIVINFO> - <DATE>1998-04-15</DATE> + <DATE>1998-09-09</DATE> </REFSYNOPSISDIVINFO> <SYNOPSIS> - CREATE AGGREGATE <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE> [AS] - ([ SFUNC1 = <REPLACEABLE CLASS="PARAMETER">state_transition_function1</REPLACEABLE> - , BASETYPE = <REPLACEABLE CLASS="PARAMETER">data_type</REPLACEABLE> - , STYPE1 = <REPLACEABLE CLASS="PARAMETER">sfunc1_return_type</REPLACEABLE> ] - [, SFUNC2 = <REPLACEABLE CLASS="PARAMETER">state_transition_function2</REPLACEABLE> - , STYPE2 = <REPLACEABLE CLASS="PARAMETER">sfunc2_return_type</REPLACEABLE> ] - [, FINALFUNC = <REPLACEABLE CLASS="PARAMETER">final_function</REPLACEABLE> ] - [, INITCOND1 = <REPLACEABLE CLASS="PARAMETER">initial_condition1</REPLACEABLE> ] - [, INITCOND2 = <REPLACEABLE CLASS="PARAMETER">initial_condition2</REPLACEABLE> ] - ) +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-04-15</DATE> + <DATE>1998-09-09</DATE> </REFSECT2INFO> <TITLE> Inputs </TITLE> - <variablelist> - <varlistentry> - <term></term> - <listitem> <PARA> <VARIABLELIST> <VARLISTENTRY> @@ -57,88 +49,115 @@ haven't done this.</comment> </para> </LISTITEM> </varlistentry> + <varlistentry> <term> - <REPLACEABLE CLASS="PARAMETER">state_transition_function1</REPLACEABLE> + <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">data_type</REPLACEABLE> + <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">state-transition_function2</REPLACEABLE> + <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">final_function</REPLACEABLE> + <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> - </listitem> - </varlistentry> - </variablelist> + </REFSECT2> <REFSECT2 ID="R2-SQL-CREATEAGGREGATE-2"> <REFSECT2INFO> - <DATE>1998-04-15</DATE> + <DATE>1998-09-09</DATE> </REFSECT2INFO> <TITLE> Outputs @@ -148,6 +167,7 @@ haven't done this.</comment> <VARIABLELIST> <VARLISTENTRY> <TERM> +<replaceable>status</replaceable> </TERM> <LISTITEM> <PARA> @@ -172,82 +192,130 @@ haven't done this.</comment> <REFSECT1 ID="R1-SQL-CREATEAGGREGATE-1"> <REFSECT1INFO> - <DATE>1998-04-15</DATE> + <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 use up to three functions, two - state transition functions, X1 and X2: - X1( internal-state1, next-data_item ) ---> next-internal-state1 - X2( internal-state2 ) ---> next-internal-state2 - and a final calculation function, F: - F(internal-state1, internal-state2) ---> aggregate-value - These functions are required to have the following properties: + 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 state-transition-function-1 must - be (stype1,basetype), and its return value must be - stype1. + 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 state-transition- - function-2 must be stype2. + 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 (stype1,stype2), and its return value must - be a POSTGRES base type (not necessarily the same - as basetype. + 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> - The final-calculation-function should be specified + FINALFUNC should be specified if and only if both state-transition functions are specified. </para </listitem> </itemizedlist> </PARA> - <para> - Note that it is possible to specify aggregate functions - that have varying combinations of state and final functions. - For example, the "count" aggregate requires sfunc2 - (an incrementing function) but not sfunc1 or finalfunc, - whereas the "sum" aggregate requires sfunc1 (an addition - function) but not sfunc2 or finalfunc and the "average" - 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> + <para> - Aggregates also require two initial conditions, one for + 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 text. + in the database as fields of type <type>text</type>. </para> <REFSECT2 ID="R2-SQL-CREATEAGGREGATE-3"> <REFSECT2INFO> - <DATE>1998-04-15</DATE> + <DATE>1998-09-09</DATE> </REFSECT2INFO> <TITLE> Notes </TITLE> - <PARA> - CREATE AGGREGATE function is a PostgreSQL language extension. - </PARA> - <para> - Refer to DROP AGGREGATE function to drop aggregate functions. + 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"> @@ -255,10 +323,10 @@ haven't done this.</comment> Usage </TITLE> <PARA> - </PARA> - <ProgramListing> - </ProgramListing> - +Refer to the chapter on aggregate functions + in the <citetitle>PostgreSQL Programmer's Guide</citetitle> + on aggregate functions for +complete examples of usage. </REFSECT1> @@ -267,17 +335,18 @@ haven't done this.</comment> Compatibility </TITLE> <PARA> - </PARA> - + <REFSECT2 ID="R2-SQL-CREATEAGGREGATE-4"> <REFSECT2INFO> -<DATE>1998-04-15</DATE> +<DATE>1998-09-09</DATE> </REFSECT2INFO> <TITLE> SQL92 </TITLE> <PARA> - There is no CREATE AGGREGATE function on SQL92. + <command>CREATE AGGREGATE</command> +is a <productname>Postgres</productname> language extension. + There is no <command>CREATE AGGREGATE</command> in SQL92. </PARA> </REFENTRY> |