On Tue, Oct 24, 2023 at 06:45:48PM -0700, David G. Johnston wrote: > I'd prefer to keep pointing out that the ones documented are those whose > outputs will vary due to ordering.
Okay, I re-added it in the attached patch, and tightened up the text. > I've been sympathetic to the user comments that we don't have enough > examples. Good point. > Just using array_agg for that purpose, showing both DISTINCT and ORDER BY > seems > like a fair compromise (removes two from my original proposal). The examples > in the section we tell them to go see aren't of that great quality. If you > strongly dislike having the function table contain the examples we should at > least improve the page we are sending them to. (As an aside to this, I've > personally always found the syntax block with the 5 syntaxes shown there to be > intimidating/hard-to-read). I think you are right that it belongs in the syntax section; we cover ordering extensively there. We already have queries there, but not output, so I moved the relevant examples to there and replaced the example that had no output. > I'd at least suggest you reconsider the commentary and examples surrounding > jsonb_object_agg. I moved that as well, and tightened the example. > The same goes for the special knowledge of floating point behavior for why > we've chosen to document avg/sum, something that typically doesn't care about > order, as having an optional order by. The floating example seems too obscure to mention in our function docs. I can put a sentence in the syntax docs, but is there value in explaining that to users? How it that helpful? Example? -- 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..d60c65f8fc 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -20273,6 +20273,13 @@ SELECT NULLIF(value, '(none)') ... aggregation. </para> + <para> + While all aggregates below accept an optional + <literal>ORDER BY</literal> clause (as outlined in <xref + linkend="syntax-aggregates"/>), the clause has only been added to + aggregates whose output is affected by ordering. + </para> + <table id="functions-aggregate-table"> <title>General-Purpose Aggregate Functions</title> <tgroup cols="2"> @@ -20310,7 +20317,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_columns</literal> ) <returnvalue>anyarray</returnvalue> </para> <para> @@ -20321,7 +20328,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_columns</literal> ) <returnvalue>anyarray</returnvalue> </para> <para> @@ -20526,14 +20533,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_columns</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_columns</literal> ) <returnvalue>jsonb</returnvalue> </para> <para> @@ -20573,7 +20580,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_columns</literal> ) <returnvalue>json</returnvalue> </para> <para role="func_signature"> @@ -20582,7 +20590,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_columns</literal> ) <returnvalue>jsonb</returnvalue> </para> <para> @@ -20819,7 +20828,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_columns</literal> ) <returnvalue>bytea</returnvalue> </para> <para> @@ -20851,11 +20861,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_columns</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_columns</literal> </optional> ) <returnvalue>double precision</returnvalue> </para> <para role="func_signature"> @@ -20877,7 +20887,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_columns</literal> ) <returnvalue>xml</returnvalue> </para> <para> diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 3ba844057f..b64733d8aa 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1647,7 +1647,26 @@ sqrt(2) are always just expressions and cannot be output-column names or numbers. For example: <programlisting> -SELECT array_agg(a ORDER BY b DESC) FROM table; +WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) ) +SELECT array_agg(v ORDER BY v DESC) FROM vals; + array_agg +------------- + {4,3,3,2,1} +</programlisting> + Since <type>jsonb</type> only keeps the last matching key, ordering + of its keys can be significant: +<programlisting> +WITH vals (k, v) AS ( VALUES ('key0','1'), ('key1','3'), ('key1','2') ) +SELECT jsonb_object_agg(k, v) FROM vals; + jsonb_object_agg +---------------------------- + {"key0": "1", "key1": "2"} + +WITH vals (k, v) AS ( VALUES ('key0','1'), ('key1','3'), ('key1','2') ) +SELECT jsonb_object_agg(k, v ORDER BY v) FROM vals; + jsonb_object_agg +---------------------------- + {"key0": "1", "key1": "3"} </programlisting> </para> @@ -1673,6 +1692,14 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect expressions must match regular arguments of the aggregate; that is, you cannot sort on an expression that is not included in the <literal>DISTINCT</literal> list. + For example: +<programlisting> +WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) ) +SELECT array_agg(DISTINCT v) FROM vals; + array_agg +----------- + {1,2,3,4} +</programlisting> </para> <note>