On Thu, Oct 26, 2023 at 03:44:14PM -0700, David G. Johnston wrote:
> On Thu, Oct 26, 2023 at 3:36 PM Bruce Momjian <br...@momjian.us> wrote:
>
> No sneaking. ;-) It would be bad to document this unevenly because it
> sets expectations in other parts of the system if we don't mention it.
>
>
> Agreed.
>
> Last suggestion, remove the first jsonb_agg example that lacks an order by.
>
> +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"}
> +
>
> We shouldn't write an example that relies on the rows being evaluated 1-2-3
> without specifying an order by clause.
Sure, done in the attached patch.
--
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..3b49e63987 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>
@@ -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..80d71d362f 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1647,7 +1647,19 @@ 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:
+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>
@@ -1668,20 +1680,19 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
</para>
<para>
- If <literal>DISTINCT</literal> is specified in addition to an
- <replaceable>order_by_clause</replaceable>, then all the <literal>ORDER BY</literal>
- 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.
+ If <literal>DISTINCT</literal> is specified with an
+ <replaceable>order_by_clause</replaceable>, <literal>ORDER
+ BY</literal> expressions can only reference columns in the
+ <literal>DISTINCT</literal> list. For example:
+<programlisting>
+WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
+SELECT array_agg(DISTINCT v ORDER BY v DESC) FROM vals;
+ array_agg
+-----------
+ {4,3,2,1}
+</programlisting>
</para>
- <note>
- <para>
- The ability to specify both <literal>DISTINCT</literal> and <literal>ORDER BY</literal>
- in an aggregate function is a <productname>PostgreSQL</productname> extension.
- </para>
- </note>
-
<para>
Placing <literal>ORDER BY</literal> within the aggregate's regular argument
list, as described so far, is used when ordering the input rows for
