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 <br...@momjian.us> 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>