Aggregate ORDER BY docs patch, first attempt

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>