diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ref/alter_aggregate.sgml | 13 | ||||
| -rw-r--r-- | doc/src/sgml/ref/comment.sgml | 9 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_aggregate.sgml | 68 | ||||
| -rw-r--r-- | doc/src/sgml/ref/drop_aggregate.sgml | 11 | ||||
| -rw-r--r-- | doc/src/sgml/ref/psql-ref.sgml | 4 | ||||
| -rw-r--r-- | doc/src/sgml/sql.sgml | 24 | ||||
| -rw-r--r-- | doc/src/sgml/syntax.sgml | 30 | ||||
| -rw-r--r-- | doc/src/sgml/xaggr.sgml | 21 |
8 files changed, 96 insertions, 84 deletions
diff --git a/doc/src/sgml/ref/alter_aggregate.sgml b/doc/src/sgml/ref/alter_aggregate.sgml index c3311b99ce7..a54969032c0 100644 --- a/doc/src/sgml/ref/alter_aggregate.sgml +++ b/doc/src/sgml/ref/alter_aggregate.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/alter_aggregate.sgml,v 1.7 2005/10/13 22:44:51 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/alter_aggregate.sgml,v 1.8 2006/07/27 19:52:04 tgl Exp $ PostgreSQL documentation --> @@ -20,9 +20,9 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable> ) RENAME TO <replaceable>new_name</replaceable> -ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable> ) OWNER TO <replaceable>new_owner</replaceable> -ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable> ) SET SCHEMA <replaceable>new_schema</replaceable> +ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable> [ , ... ] ) RENAME TO <replaceable>new_name</replaceable> +ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable> [ , ... ] ) OWNER TO <replaceable>new_owner</replaceable> +ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable> [ , ... ] ) SET SCHEMA <replaceable>new_schema</replaceable> </synopsis> </refsynopsisdiv> @@ -64,8 +64,9 @@ ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>type</replaceable <term><replaceable class="parameter">type</replaceable></term> <listitem> <para> - The argument data type of the aggregate function, or - <literal>*</literal> if the function accepts any data type. + An input data type on which the aggregate function operates. + To reference a zero-argument aggregate function, write <literal>*</> + in place of the list of input data types. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index d8c0a3acab0..c340b142c5b 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/comment.sgml,v 1.30 2006/02/12 03:22:17 momjian Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/comment.sgml,v 1.31 2006/07/27 19:52:04 tgl Exp $ PostgreSQL documentation --> @@ -24,7 +24,7 @@ COMMENT ON { TABLE <replaceable class="PARAMETER">object_name</replaceable> | COLUMN <replaceable class="PARAMETER">table_name</replaceable>.<replaceable class="PARAMETER">column_name</replaceable> | - AGGREGATE <replaceable class="PARAMETER">agg_name</replaceable> (<replaceable class="PARAMETER">agg_type</replaceable>) | + AGGREGATE <replaceable class="PARAMETER">agg_name</replaceable> (<replaceable class="PARAMETER">agg_type</replaceable> [, ...] ) | CAST (<replaceable>sourcetype</replaceable> AS <replaceable>targettype</replaceable>) | CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ON <replaceable class="PARAMETER">table_name</replaceable> | CONVERSION <replaceable class="PARAMETER">object_name</replaceable> | @@ -101,8 +101,9 @@ COMMENT ON <term><replaceable class="parameter">agg_type</replaceable></term> <listitem> <para> - The argument data type of the aggregate function, or - <literal>*</literal> if the function accepts any data type. + An input data type on which the aggregate function operates. + To reference a zero-argument aggregate function, write <literal>*</> + in place of the list of input data types. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/create_aggregate.sgml b/doc/src/sgml/ref/create_aggregate.sgml index 5eb0741c9c2..b43636c9bfa 100644 --- a/doc/src/sgml/ref/create_aggregate.sgml +++ b/doc/src/sgml/ref/create_aggregate.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/create_aggregate.sgml,v 1.34 2006/04/15 17:45:18 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/create_aggregate.sgml,v 1.35 2006/07/27 19:52:04 tgl Exp $ PostgreSQL documentation --> @@ -20,7 +20,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> ( <replaceable class="PARAMETER">input_data_type</replaceable> ) ( +CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> ( <replaceable class="PARAMETER">input_data_type</replaceable> [ , ... ] ) ( SFUNC = <replaceable class="PARAMETER">sfunc</replaceable>, STYPE = <replaceable class="PARAMETER">state_data_type</replaceable> [ , FINALFUNC = <replaceable class="PARAMETER">ffunc</replaceable> ] @@ -60,16 +60,16 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> ( </para> <para> - An aggregate function is identified by its name and input data type. + An aggregate function is identified by its name and input data type(s). 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 + name and input data type(s) 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 + An aggregate function is made from one or two ordinary functions: a state transition function <replaceable class="PARAMETER">sfunc</replaceable>, @@ -77,7 +77,7 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> ( <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">sfunc</replaceable>( internal-state, next-data-values ) ---> next-internal-state <replaceable class="PARAMETER">ffunc</replaceable>( internal-state ) ---> aggregate-value </programlisting> </para> @@ -85,10 +85,11 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> ( <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, + to hold the current internal state of the aggregate. At each input row, + the aggregate argument value(s) are calculated and + the state transition function is invoked with the current state value + and the new argument value(s) to calculate a new + internal state value. After all the rows have been processed, the final function is invoked once to calculate the aggregate's return value. If there is no final function then the ending state value is returned as-is. @@ -106,15 +107,16 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> ( <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 - nonnull input value replaces the state value, and the transition - function is invoked beginning with the second nonnull input value. + function, aggregate execution behaves as follows. Rows with any 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 at the first row + with all-nonnull input values, the first argument value replaces the state + value, and the transition function is invoked at subsequent rows with + all-nonnull input values. This is handy for implementing aggregates like <function>max</function>. Note that this behavior is only available when <replaceable class="PARAMETER">state_data_type</replaceable> - is the same as + is the same as the first <replaceable class="PARAMETER">input_data_type</replaceable>. When these types are different, you must supply a nonnull initial condition or use a nonstrict transition function. @@ -122,7 +124,7 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> ( <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 + unconditionally at each input row, 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 null values. </para> @@ -180,10 +182,10 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; <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 <literal>*</> for an aggregate that - does not examine its input values (an example is - <function>count(*)</function>). + An input data type on which this aggregate function operates. + To create a zero-argument aggregate function, write <literal>*</> + in place of the list of input data types. (An example of such an + aggregate is <function>count(*)</function>.) </para> </listitem> </varlistentry> @@ -195,8 +197,8 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; In the old syntax for <command>CREATE AGGREGATE</>, the input data type is specified by a <literal>basetype</> parameter rather than being written next to the aggregate name. Note that this syntax allows - only one input parameter. To define an aggregate that does not examine - its input values, specify the <literal>basetype</> as + only one input parameter. To define a zero-argument aggregate function, + specify the <literal>basetype</> as <literal>"ANY"</> (not <literal>*</>). </para> </listitem> @@ -207,17 +209,15 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; <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, + input row. For an <replaceable class="PARAMETER">N</>-argument + aggregate function, the <replaceable class="PARAMETER">sfunc</> + must take <replaceable class="PARAMETER">N</>+1 arguments, the first being of type <replaceable - class="PARAMETER">state_data_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_data_type</replaceable>. In either case - the function must return a value of type <replaceable + class="PARAMETER">state_data_type</replaceable> and the rest + matching the declared input data type(s) of the aggregate. + The function must return a value of type <replaceable class="PARAMETER">state_data_type</replaceable>. This function - takes the current state value and the current input data item, + takes the current state value and the current input data value(s), and returns the next state value. </para> </listitem> @@ -237,7 +237,7 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; <listitem> <para> The name of the final function called to compute the aggregate's - result after all input data has been traversed. The function + result after all input rows have been traversed. The function must take a single argument of type <replaceable class="PARAMETER">state_data_type</replaceable>. The return data type of the aggregate is defined as the return type of this @@ -269,7 +269,7 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; <function>MAX</>-like aggregate. This is just an operator name (possibly schema-qualified). The operator is assumed to have the same input data types as - the aggregate. + the aggregate (which must be a single-argument aggregate). </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/drop_aggregate.sgml b/doc/src/sgml/ref/drop_aggregate.sgml index e6d03e0bdab..749c06525ae 100644 --- a/doc/src/sgml/ref/drop_aggregate.sgml +++ b/doc/src/sgml/ref/drop_aggregate.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/drop_aggregate.sgml,v 1.28 2006/06/16 22:27:55 adunstan Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/drop_aggregate.sgml,v 1.29 2006/07/27 19:52:04 tgl Exp $ PostgreSQL documentation --> @@ -20,7 +20,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -DROP AGGREGATE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> ( <replaceable class="PARAMETER">type</replaceable> ) [ CASCADE | RESTRICT ] +DROP AGGREGATE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> ( <replaceable class="PARAMETER">type</replaceable> [ , ... ] ) [ CASCADE | RESTRICT ] </synopsis> </refsynopsisdiv> @@ -43,7 +43,7 @@ DROP AGGREGATE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> ( <term><literal>IF EXISTS</literal></term> <listitem> <para> - Do not throw an error if the aggregate does not exist. A notice is issued + Do not throw an error if the aggregate does not exist. A notice is issued in this case. </para> </listitem> @@ -62,8 +62,9 @@ DROP AGGREGATE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> ( <term><replaceable class="parameter">type</replaceable></term> <listitem> <para> - The argument data type of the aggregate function, or - <literal>*</literal> if the function accepts any data type. + An input data type on which the aggregate function operates. + To reference a zero-argument aggregate function, write <literal>*</> + in place of the list of input data types. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 74a53368463..97809bb7b19 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.165 2006/05/31 22:34:35 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.166 2006/07/27 19:52:04 tgl Exp $ PostgreSQL documentation --> @@ -854,7 +854,7 @@ testdb=> <listitem> <para> Lists all available aggregate functions, together with the data - type they operate on. If <replaceable + types they operate on. If <replaceable class="parameter">pattern</replaceable> is specified, only aggregates whose names match the pattern are shown. </para> diff --git a/doc/src/sgml/sql.sgml b/doc/src/sgml/sql.sgml index b27400b4a45..d8b2934063d 100644 --- a/doc/src/sgml/sql.sgml +++ b/doc/src/sgml/sql.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/sql.sgml,v 1.40 2006/04/30 18:30:38 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/sql.sgml,v 1.41 2006/07/27 19:52:04 tgl Exp $ --> <chapter id="sql-intro"> <title>SQL</title> @@ -1247,13 +1247,13 @@ select sname, pname from supplier </sect3> <sect3> - <title id="aggregates-tutorial">Aggregate Operators</title> + <title id="aggregates-tutorial">Aggregate Functions</title> <para> - <acronym>SQL</acronym> provides aggregate operators (e.g. AVG, - COUNT, SUM, MIN, MAX) that take an expression as argument. The - expression is evaluated at each row that satisfies the WHERE - clause, and the aggregate operator is calculated over this set + <acronym>SQL</acronym> provides aggregate functions such as AVG, + COUNT, SUM, MIN, and MAX. The argument(s) of an aggregate function + are evaluated at each row that satisfies the WHERE + clause, and the aggregate function is calculated over this set of input values. Normally, an aggregate delivers a single result for a whole <command>SELECT</command> statement. But if grouping is specified in the query, then a separate calculation @@ -1311,10 +1311,10 @@ SELECT COUNT(PNO) <para> <acronym>SQL</acronym> allows one to partition the tuples of a table into groups. Then the - aggregate operators described above can be applied to the groups — - i.e. the value of the aggregate operator is no longer calculated over + aggregate functions described above can be applied to the groups — + i.e. the value of the aggregate function is no longer calculated over all the values of the specified column but over all values of a - group. Thus the aggregate operator is evaluated separately for every + group. Thus the aggregate function is evaluated separately for every group. </para> @@ -1396,7 +1396,7 @@ SELECT S.SNO, S.SNAME, COUNT(SE.PNO) <para> In our example we got four groups and now we can apply the aggregate - operator COUNT to every group leading to the final result of the query + function COUNT to every group leading to the final result of the query given above. </para> </example> @@ -1404,9 +1404,9 @@ SELECT S.SNO, S.SNAME, COUNT(SE.PNO) <para> Note that for a query using GROUP BY and aggregate - operators to make sense the target list can only refer directly to + functions to make sense, the target list can only refer directly to the attributes being grouped by. Other attributes may only be used - inside the argument of an aggregate function. Otherwise there would + inside the arguments of aggregate functions. Otherwise there would not be a unique value to associate with the other attributes. </para> diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 74cc813a681..58c9ddad6d5 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.107 2006/06/26 17:24:40 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.108 2006/07/27 19:52:04 tgl Exp $ --> <chapter id="sql-syntax"> <title>SQL Syntax</title> @@ -673,8 +673,9 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> ) <para> The asterisk (<literal>*</literal>) is used in some contexts to denote all the fields of a table row or composite value. It also - has a special meaning when used as the argument of the - <function>COUNT</function> aggregate function. + has a special meaning when used as the argument of an + aggregate function, namely that the aggregate does not require + any explicit parameter. </para> </listitem> @@ -1269,9 +1270,9 @@ sqrt(2) syntax of an aggregate expression is one of the following: <synopsis> -<replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable>) -<replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable>) -<replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable>) +<replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable> [ , ... ] ) +<replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable> [ , ... ] ) +<replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable> [ , ... ] ) <replaceable>aggregate_name</replaceable> ( * ) </synopsis> @@ -1284,16 +1285,16 @@ sqrt(2) <para> The first form of aggregate expression invokes the aggregate - across all input rows for which the given expression yields a - non-null value. (Actually, it is up to the aggregate function + across all input rows for which the given expression(s) yield + non-null values. (Actually, it is up to the aggregate function whether to ignore null values or not — but all the standard ones do.) The second form is the same as the first, since <literal>ALL</literal> is the default. The third form invokes the - aggregate for all distinct non-null values of the expression found + aggregate for all distinct non-null values of the expressions found in the input rows. The last form invokes the aggregate once for each input row regardless of null or non-null values; since no particular input value is specified, it is generally only useful - for the <function>count()</function> aggregate function. + for the <function>count(*)</function> aggregate function. </para> <para> @@ -1323,7 +1324,7 @@ sqrt(2) <xref linkend="sql-syntax-scalar-subqueries"> and <xref linkend="functions-subquery">), the aggregate is normally evaluated over the rows of the subquery. But an exception occurs - if the aggregate's argument contains only outer-level variables: + if the aggregate's arguments contain only outer-level variables: the aggregate then belongs to the nearest such outer level, and is evaluated over the rows of that query. The aggregate expression as a whole is then an outer reference for the subquery it appears in, @@ -1332,6 +1333,13 @@ sqrt(2) appearing only in the result list or <literal>HAVING</> clause applies with respect to the query level that the aggregate belongs to. </para> + + <note> + <para> + <productname>PostgreSQL</productname> currently does not support + <literal>DISTINCT</> with more than one input expression. + </para> + </note> </sect2> <sect2 id="sql-syntax-type-casts"> diff --git a/doc/src/sgml/xaggr.sgml b/doc/src/sgml/xaggr.sgml index db6c077cbf6..c02f21048a7 100644 --- a/doc/src/sgml/xaggr.sgml +++ b/doc/src/sgml/xaggr.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/xaggr.sgml,v 1.31 2006/04/15 17:45:33 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/xaggr.sgml,v 1.32 2006/07/27 19:52:04 tgl Exp $ --> <sect1 id="xaggr"> <title>User-Defined Aggregates</title> @@ -10,11 +10,11 @@ <para> Aggregate functions in <productname>PostgreSQL</productname> - are expressed as <firstterm>state values</firstterm> + are expressed in terms of <firstterm>state values</firstterm> and <firstterm>state transition functions</firstterm>. - That is, an aggregate can be - defined in terms of state that is modified whenever an - input item is processed. To define a new aggregate + That is, an aggregate operates using a state value that is updated + as each successive input row is processed. + To define a new aggregate function, one selects a data type for the state value, an initial value for the state, and a state transition function. The state transition function is just an @@ -85,13 +85,14 @@ SELECT sum(a) FROM test_complex; Another bit of default behavior for a <quote>strict</> transition function is that the previous state value is retained unchanged whenever a null input value is encountered. Thus, null values are ignored. If you - need some other behavior for null inputs, just do not define your transition - function as strict, and code it to test for null inputs and do - whatever is needed. + need some other behavior for null inputs, do not declare your + transition function as strict; instead code it to test for null inputs and + do whatever is needed. </para> <para> - <function>avg</> (average) is a more complex example of an aggregate. It requires + <function>avg</> (average) is a more complex example of an aggregate. + It requires two pieces of running state: the sum of the inputs and the count of the number of inputs. The final result is obtained by dividing these quantities. Average is typically implemented by using a @@ -117,7 +118,7 @@ CREATE AGGREGATE avg (float8) See <xref linkend="extend-types-polymorphic"> for an explanation of polymorphic functions. Going a step further, the aggregate function itself may be specified - with a polymorphic input type and state type, allowing a single + with polymorphic input type(s) and state type, allowing a single aggregate definition to serve for multiple input data types. Here is an example of a polymorphic aggregate: |