[Vik Fearing <vik@postgresfriends.org>]

The SQL:2023 Standard defines a new aggregate named ANY_VALUE.  It 
returns an implementation-dependent (i.e. non-deterministic) value from 
the rows in its group.

PFA an implementation of this aggregate.

Ideally, the transition function would stop being called after the first 
non-null was found, and then the entire aggregation would stop when all 
functions say they are finished[*], but this patch does not go anywhere 
near that far.

This patch is based off of commit fb958b5da8.

[*] I can imagine something like array_agg(c ORDER BY x LIMIT 5) to get 
the top five of something without going through a LATERAL subquery.
-- 
Vik Fearing

Attachments:

0001-Implement-ANY_VALUE-aggregate.patch (text/x-patch, 7.3K) download
From 7465fac12fc636ff26088ae31de2937f7c3a459f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Sat, 9 Apr 2022 00:07:38 +0200
Subject: [PATCH] Implement ANY_VALUE aggregate

SQL:2023 defines an ANY_VALUE aggregate whose purpose is to emit an
implementation-dependent (i.e. non-deterministic) value from the
aggregated rows.
---
 doc/src/sgml/func.sgml                   | 14 ++++++++++++++
 src/backend/utils/adt/misc.c             | 12 ++++++++++++
 src/include/catalog/pg_aggregate.dat     |  4 ++++
 src/include/catalog/pg_proc.dat          |  8 ++++++++
 src/test/regress/expected/aggregates.out | 18 ++++++++++++++++++
 src/test/regress/sql/aggregates.sql      |  5 +++++
 6 files changed, 61 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2052d3c844..1823ee71d7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19706,16 +19706,30 @@ SELECT NULLIF(value, '(none)') ...
        <para>
         Description
        </para></entry>
        <entry>Partial Mode</entry>
       </row>
      </thead>
 
      <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>any_value</primary>
+        </indexterm>
+        <function>any_value</function> ( <type>"any"</type> )
+        <returnvalue><replaceable>same as input type</replaceable></returnvalue>
+       </para>
+       <para>
+        Chooses a non-deterministic value from the non-null input values.
+       </para></entry>
+       <entry>Yes</entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>array_agg</primary>
         </indexterm>
         <function>array_agg</function> ( <type>anynonarray</type> )
         <returnvalue>anyarray</returnvalue>
        </para>
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 9c13251231..94c92de06d 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -928,8 +928,20 @@ pg_get_replica_identity_index(PG_FUNCTION_ARGS)
 	idxoid = RelationGetReplicaIndex(rel);
 	table_close(rel, AccessShareLock);
 
 	if (OidIsValid(idxoid))
 		PG_RETURN_OID(idxoid);
 	else
 		PG_RETURN_NULL();
 }
+
+Datum
+any_value_trans(PG_FUNCTION_ARGS)
+{
+	/* Return the first non-null argument */
+	if (!PG_ARGISNULL(0))
+		PG_RETURN_DATUM(PG_GETARG_DATUM(0));
+	if (!PG_ARGISNULL(1))
+		PG_RETURN_DATUM(PG_GETARG_DATUM(1));
+	PG_RETURN_NULL();
+}
+
diff --git a/src/include/catalog/pg_aggregate.dat b/src/include/catalog/pg_aggregate.dat
index b9110a5298..37626d6f0c 100644
--- a/src/include/catalog/pg_aggregate.dat
+++ b/src/include/catalog/pg_aggregate.dat
@@ -620,9 +620,13 @@
   aggtransfn => 'ordered_set_transition_multi', aggfinalfn => 'cume_dist_final',
   aggfinalextra => 't', aggfinalmodify => 'w', aggmfinalmodify => 'w',
   aggtranstype => 'internal' },
 { aggfnoid => 'dense_rank(any)', aggkind => 'h', aggnumdirectargs => '1',
   aggtransfn => 'ordered_set_transition_multi',
   aggfinalfn => 'dense_rank_final', aggfinalextra => 't', aggfinalmodify => 'w',
   aggmfinalmodify => 'w', aggtranstype => 'internal' },
 
+# any_value
+{ aggfnoid => 'any_value(anyelement)', aggtransfn => 'any_value_trans',
+  aggcombinefn => 'any_value_trans', aggtranstype => 'anyelement' },
+
 ]
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f9301b2627..2ee4797559 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11849,9 +11849,17 @@
   proname => 'brin_minmax_multi_summary_recv', provolatile => 's',
   prorettype => 'pg_brin_minmax_multi_summary', proargtypes => 'internal',
   prosrc => 'brin_minmax_multi_summary_recv' },
 { oid => '4641', descr => 'I/O',
   proname => 'brin_minmax_multi_summary_send', provolatile => 's',
   prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary',
   prosrc => 'brin_minmax_multi_summary_send' },
 
+{ oid => '8981', descr => 'arbitrary value from among input values',
+  proname => 'any_value', prokind => 'a', proisstrict => 'f',
+  prorettype => 'anyelement', proargtypes => 'anyelement',
+  prosrc => 'aggregate_dummy' },
+{ oid => '8982', descr => 'any_value transition function',
+  proname => 'any_value_trans', prorettype => 'anyelement', proargtypes => 'anyelement anyelement',
+  prosrc => 'any_value_trans' },
+
 ]
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index fc2bd40be2..fb87b9abf1 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -20,16 +20,28 @@ SELECT avg(four) AS avg_1 FROM onek;
 (1 row)
 
 SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
        avg_32        
 ---------------------
  32.6666666666666667
 (1 row)
 
+SELECT any_value(v) FROM (VALUES (1)) AS v (v);
+ any_value 
+-----------
+         1
+(1 row)
+
+SELECT any_value(v) FROM (VALUES (NULL)) AS v (v);
+ any_value 
+-----------
+ 
+(1 row)
+
 -- In 7.1, avg(float4) is computed using float8 arithmetic.
 -- Round the result to 3 digits to avoid platform-specific results.
 SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
  avg_107_943 
 -------------
      107.943
 (1 row)
 
@@ -1875,16 +1887,22 @@ having exists (select 1 from onek b where sum(distinct a.four) = b.four);
 
 select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0')
 from (values ('a', 'b')) AS v(foo,bar);
  max 
 -----
  a
 (1 row)
 
+SELECT any_value(v) FILTER (WHERE v > 2) FROM (VALUES (1), (2), (3)) AS v (v);
+ any_value 
+-----------
+         3
+(1 row)
+
 -- outer reference in FILTER (PostgreSQL extension)
 select (select count(*)
         from (values (1)) t0(inner_c))
 from (values (2),(3)) t1(outer_c); -- inner query is aggregation query
  count 
 -------
      1
      1
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index a4c00ff7a9..7206e475a1 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -19,16 +19,19 @@ COPY aggtest FROM :'filename';
 
 ANALYZE aggtest;
 
 
 SELECT avg(four) AS avg_1 FROM onek;
 
 SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
 
+SELECT any_value(v) FROM (VALUES (1)) AS v (v);
+SELECT any_value(v) FROM (VALUES (NULL)) AS v (v);
+
 -- In 7.1, avg(float4) is computed using float8 arithmetic.
 -- Round the result to 3 digits to avoid platform-specific results.
 
 SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
 
 SELECT avg(gpa) AS avg_3_4 FROM ONLY student;
 
 
@@ -711,16 +714,18 @@ group by ten;
 
 select ten, sum(distinct four) filter (where four > 10) from onek a
 group by ten
 having exists (select 1 from onek b where sum(distinct a.four) = b.four);
 
 select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0')
 from (values ('a', 'b')) AS v(foo,bar);
 
+SELECT any_value(v) FILTER (WHERE v > 2) FROM (VALUES (1), (2), (3)) AS v (v);
+
 -- outer reference in FILTER (PostgreSQL extension)
 select (select count(*)
         from (values (1)) t0(inner_c))
 from (values (2),(3)) t1(outer_c); -- inner query is aggregation query
 select (select count(*) filter (where outer_c <> 0)
         from (values (1)) t0(inner_c))
 from (values (2),(3)) t1(outer_c); -- outer query is aggregation query
 select (select count(inner_c) filter (where outer_c <> 0)
-- 
2.34.1