On Tue, Dec 13, 2022 at 07:38:15PM -0700, David G. Johnston wrote:
> All,
>
> The recent discussion surrounding aggregates and ORDER BY moved me to look
> over
> our existing documentation, especially now that we've reworked the function
> tables, to see what improvements can be had by simply documenting those
> functions where ORDER BY may change the user-visible output. I skipped range
> aggregates for the moment but handled the others on the aggregates page (not
> window functions). This includes the float types for sum and avg.
>
> I added a note just before the table linking back to the syntax chapter and
> describing the newly added rules and syntax choice in the table.
>
> The nuances of floating point math suggest to me that specifying order by for
> those is in some kind of gray area and so I've marked it optional...any
> suggestions for wording (or an xref) to explain those nuances or should it
> just
> be shown non-optional like the others? Or not shown at all?
>
> The novelty of my examples is up for bikeshedding. I didn't want anything too
> long so a subquery didn't make sense, and I was trying to avoid duplication as
> well as multiple lines - hence creating a CTE that can be copied onto all of
> the example queries to produce the noted result.
>
> I added a DISTINCT example to array_agg because it is the first aggregate on
> the page and so hopefully will be seen during a cursory reading. Plus,
> array_agg is the go-to function for doing this kind of experimentation.
I like this idea, though the examples seemed too detailed so I skipped
them. Here is the trimmed-down patch I would like to apply.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7c3e940afe..35d8924c6b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20273,6 +20273,18 @@ SELECT NULLIF(value, '(none)') ...
aggregation.
</para>
+ <note>
+ <para>
+ The aggregates in this section all accept an optional <literal>ORDER
+ BY</literal> clause as described in the general syntax rules
+ for aggregates (see <xref linkend="syntax-aggregates" />).
+ <literal>input_sort_expr</literal> specifies the column names to sort
+ by before being supplied to the aggreagate function. For example,
+ <literal>array_agg(... ORDER BY ...)</literal> produces an array with
+ ordered values.
+ </para>
+ </note>
+
<table id="functions-aggregate-table">
<title>General-Purpose Aggregate Functions</title>
<tgroup cols="2">
@@ -20310,7 +20322,7 @@ SELECT NULLIF(value, '(none)') ...
<indexterm>
<primary>array_agg</primary>
</indexterm>
- <function>array_agg</function> ( <type>anynonarray</type> )
+ <function>array_agg</function> ( <type>anynonarray</type> <literal>ORDER BY</literal> <literal>input_sort_expr</literal>)
<returnvalue>anyarray</returnvalue>
</para>
<para>
@@ -20321,7 +20333,7 @@ SELECT NULLIF(value, '(none)') ...
<row>
<entry role="func_table_entry"><para role="func_signature">
- <function>array_agg</function> ( <type>anyarray</type> )
+ <function>array_agg</function> ( <type>anyarray</type> <literal>ORDER BY</literal> <literal>input_sort_expr</literal> )
<returnvalue>anyarray</returnvalue>
</para>
<para>
@@ -20356,11 +20368,11 @@ SELECT NULLIF(value, '(none)') ...
<returnvalue>numeric</returnvalue>
</para>
<para role="func_signature">
- <function>avg</function> ( <type>real</type> )
+ <function>avg</function> ( <type>real</type> <optional> <literal>ORDER BY</literal> <literal>input_sort_expr</literal> </optional> )
<returnvalue>double precision</returnvalue>
</para>
<para role="func_signature">
- <function>avg</function> ( <type>double precision</type> )
+ <function>avg</function> ( <type>double precision</type> <optional> <literal>ORDER BY</literal> <literal>input_sort_expr</literal> </optional> )
<returnvalue>double precision</returnvalue>
</para>
<para role="func_signature">
@@ -20526,14 +20538,14 @@ SELECT NULLIF(value, '(none)') ...
<indexterm>
<primary>json_agg</primary>
</indexterm>
- <function>json_agg</function> ( <type>anyelement</type> )
+ <function>json_agg</function> ( <type>anyelement</type> <literal>ORDER BY</literal> <literal>input_sort_expr</literal> )
<returnvalue>json</returnvalue>
</para>
<para role="func_signature">
<indexterm>
<primary>jsonb_agg</primary>
</indexterm>
- <function>jsonb_agg</function> ( <type>anyelement</type> )
+ <function>jsonb_agg</function> ( <type>anyelement</type> <literal>ORDER BY</literal> <literal>input_sort_expr</literal> )
<returnvalue>jsonb</returnvalue>
</para>
<para>
@@ -20573,7 +20585,8 @@ SELECT NULLIF(value, '(none)') ...
</indexterm>
<function>json_object_agg</function> ( <parameter>key</parameter>
<type>"any"</type>, <parameter>value</parameter>
- <type>"any"</type> )
+ <type>"any"</type>
+ <literal>ORDER BY</literal> <literal>input_sort_expr</literal> )
<returnvalue>json</returnvalue>
</para>
<para role="func_signature">
@@ -20582,7 +20595,8 @@ SELECT NULLIF(value, '(none)') ...
</indexterm>
<function>jsonb_object_agg</function> ( <parameter>key</parameter>
<type>"any"</type>, <parameter>value</parameter>
- <type>"any"</type> )
+ <type>"any"</type>
+ <literal>ORDER BY</literal> <literal>input_sort_expr</literal> )
<returnvalue>jsonb</returnvalue>
</para>
<para>
@@ -20819,7 +20833,8 @@ SELECT NULLIF(value, '(none)') ...
</para>
<para role="func_signature">
<function>string_agg</function> ( <parameter>value</parameter>
- <type>bytea</type>, <parameter>delimiter</parameter> <type>bytea</type> )
+ <type>bytea</type>, <parameter>delimiter</parameter> <type>bytea</type>
+ <literal>ORDER BY</literal> <literal>input_sort_expr</literal> )
<returnvalue>bytea</returnvalue>
</para>
<para>
@@ -20851,11 +20866,11 @@ SELECT NULLIF(value, '(none)') ...
<returnvalue>numeric</returnvalue>
</para>
<para role="func_signature">
- <function>sum</function> ( <type>real</type> )
+ <function>sum</function> ( <type>real</type> <optional> <literal>ORDER BY</literal> <literal>input_sort_expr</literal> </optional> )
<returnvalue>real</returnvalue>
</para>
<para role="func_signature">
- <function>sum</function> ( <type>double precision</type> )
+ <function>sum</function> ( <type>double precision</type> <optional> <literal>ORDER BY</literal> <literal>input_sort_expr</literal> </optional> )
<returnvalue>double precision</returnvalue>
</para>
<para role="func_signature">
@@ -20877,7 +20892,7 @@ SELECT NULLIF(value, '(none)') ...
<indexterm>
<primary>xmlagg</primary>
</indexterm>
- <function>xmlagg</function> ( <type>xml</type> )
+ <function>xmlagg</function> ( <type>xml</type> <literal>ORDER BY</literal> <literal>input_sort_expr</literal> )
<returnvalue>xml</returnvalue>
</para>
<para>
