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 <[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..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>
