First attempt at a docs patch for aggregate order by.
--
Andrew.
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.491
diff -c -r1.491 func.sgml
*** doc/src/sgml/func.sgml 21 Oct 2009 20:38:58 -0000 1.491
--- doc/src/sgml/func.sgml 13 Nov 2009 23:37:45 -0000
***************
*** 8459,8476 ****
</para>
<para>
! To determine the order of the concatenation, something like the
! following approach can be used:
<screen><![CDATA[
! SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
xmlagg
----------------------
<bar/><foo>abc</foo>
]]></screen>
! Again, see <xref linkend="functions-aggregate"> for additional
! information.
</para>
</sect3>
--- 8459,8487 ----
</para>
<para>
! To determine the order of the concatenation, an <literal>ORDER BY</>
! clause may be added to the aggregate call as described in
! <xref linkend="syntax-aggregates">. For example:
<screen><![CDATA[
! SELECT xmlagg(x ORDER BY y DESC) FROM test;
xmlagg
----------------------
<bar/><foo>abc</foo>
]]></screen>
+ </para>
+
+ <para>
+ The following non-standard approach used to be recommended
+ in previous versions, and may still be useful in specific
+ cases:
! <screen><![CDATA[
! SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
! xmlagg
! ----------------------
! <bar/><foo>abc</foo>
! ]]></screen>
</para>
</sect3>
***************
*** 9887,9906 ****
The aggregate functions <function>array_agg</function>
and <function>xmlagg</function>, as well as similar user-defined
aggregate functions, produce meaningfully different result values
! depending on the order of the input values. In the current
! implementation, the order of the input is in principle unspecified.
! Supplying the input values from a sorted subquery
! will usually work, however. For example:
<screen><![CDATA[
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
]]></screen>
But this syntax is not allowed in the SQL standard, and is
! not portable to other database systems. A future version of
! <productname>PostgreSQL</> might provide an additional feature to control
! the order in a better-defined way (<literal>xmlagg(expr ORDER BY expr, expr,
! ...)</literal>).
</para>
<para>
--- 9898,9919 ----
The aggregate functions <function>array_agg</function>
and <function>xmlagg</function>, as well as similar user-defined
aggregate functions, produce meaningfully different result values
! depending on the order of the input values. This ordering is
! unspecified by default, but may be controlled by an
! <literal>ORDER BY</> clause within the aggregate call as shown in
! <xref linkend="syntax-aggregates">.
! </para>
!
! <para>
! Alternatively, supplying the input values from a sorted subquery
! will usually work. For example:
<screen><![CDATA[
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
]]></screen>
But this syntax is not allowed in the SQL standard, and is
! not portable to other database systems.
</para>
<para>
Index: doc/src/sgml/syntax.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/syntax.sgml,v
retrieving revision 1.138
diff -c -r1.138 syntax.sgml
*** doc/src/sgml/syntax.sgml 5 Nov 2009 23:24:22 -0000 1.138
--- doc/src/sgml/syntax.sgml 13 Nov 2009 23:37:48 -0000
***************
*** 1525,1541 ****
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> ( * )
</synopsis>
where <replaceable>aggregate_name</replaceable> is a previously
! defined aggregate (possibly qualified with a schema name), and
<replaceable>expression</replaceable> is
any value expression that does not itself contain an aggregate
! expression or a window function call.
</para>
<para>
--- 1525,1544 ----
syntax of an aggregate expression is one of the following:
<synopsis>
! <replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] )
! <replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] )
! <replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] )
<replaceable>aggregate_name</replaceable> ( * )
</synopsis>
where <replaceable>aggregate_name</replaceable> is a previously
! defined aggregate (possibly qualified with a schema name),
<replaceable>expression</replaceable> is
any value expression that does not itself contain an aggregate
! expression or a window function call, and
! <replaceable>order_by_clause</replaceable> is a optional
! <literal>ORDER BY</> clause as specified in <xref linkend="queries-order">
! (though output column labels and ordinal column positions are excluded).
</para>
<para>
***************
*** 1561,1566 ****
--- 1564,1600 ----
</para>
<para>
+ Where the result of an aggregate function (such as <function>array_agg</>
+ or <function>xmlagg</>) depends on the ordering of its input, the optional
+ <replaceable>order_by_clause</> may be used to specify the desired ordering.
+ (If the clause is omitted, the ordering is unspecified.) All features
+ normally available in an <literal>ORDER BY</> clause may be used, with the
+ exception of column name aliases or ordinal positions as sort expressions.
+ </para>
+
+ <para>
+ For example:
+
+ <programlisting>
+ SELECT array_agg(a ORDER BY b DESC NULLS LAST) FROM table;
+ </programlisting>
+ </para>
+
+ <para>
+ If <literal>DISTINCT</> is specified in addition to an <replaceable>order_by_clause</>,
+ then all the aggregate parameters must appear in the <replaceable>order_by_clause</>
+ prior to any other ordering expressions. (It is not necessary to include all the
+ aggregate parameters if no other expressions are present.)
+ </para>
+
+ <note>
+ <para>
+ The ability to specify both <literal>DISTINCT</> and <literal>ORDER BY</> in
+ an aggregate function is a <productname>PostgreSQL</> extension.
+ </para>
+ </note>
+
+ <para>
The predefined aggregate functions are described in <xref
linkend="functions-aggregate">. Other aggregate functions can be added
by the user.
***************
*** 1588,1600 ****
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="syntax-window-functions">
--- 1622,1627 ----
***************
*** 1697,1703 ****
<literal>count(*) OVER (PARTITION BY x ORDER BY y)</>.
<literal>*</> is customarily not used for non-aggregate window functions.
Aggregate window functions, unlike normal aggregate functions, do not
! allow <literal>DISTINCT</> to be used within the function argument list.
</para>
<para>
--- 1724,1731 ----
<literal>count(*) OVER (PARTITION BY x ORDER BY y)</>.
<literal>*</> is customarily not used for non-aggregate window functions.
Aggregate window functions, unlike normal aggregate functions, do not
! allow <literal>DISTINCT</> or <literal>ORDER BY</> to be used within the
! function argument list.
</para>
<para>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
