Skip to content

Navigation Menu

Sign in

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 2ddab01

Browse filesBrowse files
peterepetere
committed
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. Author: Vik Fearing <vik@postgresfriends.org> Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com> Reviewed-by: David Rowley <dgrowleyml@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/5cff866c-10a8-d2df-32cb-e9072e6b04a2@postgresfriends.org
1 parent 7e5ddf7 commit 2ddab01
Copy full SHA for 2ddab01

File tree

8 files changed

+74
-1
lines changed
Filter options

8 files changed

+74
-1
lines changed

‎doc/src/sgml/func.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/func.sgml
+14Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19735,6 +19735,20 @@ SELECT NULLIF(value, '(none)') ...
1973519735
</thead>
1973619736

1973719737
<tbody>
19738+
<row>
19739+
<entry role="func_table_entry"><para role="func_signature">
19740+
<indexterm>
19741+
<primary>any_value</primary>
19742+
</indexterm>
19743+
<function>any_value</function> ( <type>anyelement</type> )
19744+
<returnvalue><replaceable>same as input type</replaceable></returnvalue>
19745+
</para>
19746+
<para>
19747+
Returns an arbitrary value from the non-null input values.
19748+
</para></entry>
19749+
<entry>Yes</entry>
19750+
</row>
19751+
1973819752
<row>
1973919753
<entry role="func_table_entry"><para role="func_signature">
1974019754
<indexterm>

‎src/backend/catalog/sql_features.txt

Copy file name to clipboardExpand all lines: src/backend/catalog/sql_features.txt
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -520,6 +520,7 @@ T622 Trigonometric functions YES
520520
T623 General logarithm functions YES
521521
T624 Common logarithm functions YES
522522
T625 LISTAGG NO
523+
T626 ANY_VALUE YES SQL:202x draft
523524
T631 IN predicate with one list element YES
524525
T641 Multiple column assignment NO only some syntax variants supported
525526
T651 SQL-schema statements in SQL routines YES

‎src/backend/utils/adt/misc.c

Copy file name to clipboardExpand all lines: src/backend/utils/adt/misc.c
+9Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1041,3 +1041,12 @@ pg_get_replica_identity_index(PG_FUNCTION_ARGS)
10411041
else
10421042
PG_RETURN_NULL();
10431043
}
1044+
1045+
/*
1046+
* Transition function for the ANY_VALUE aggregate
1047+
*/
1048+
Datum
1049+
any_value_transfn(PG_FUNCTION_ARGS)
1050+
{
1051+
PG_RETURN_DATUM(PG_GETARG_DATUM(0));
1052+
}

‎src/include/catalog/catversion.h

Copy file name to clipboardExpand all lines: src/include/catalog/catversion.h
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202302111
60+
#define CATALOG_VERSION_NO 202302221
6161

6262
#endif

‎src/include/catalog/pg_aggregate.dat

Copy file name to clipboardExpand all lines: src/include/catalog/pg_aggregate.dat
+4Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -634,4 +634,8 @@
634634
aggfinalfn => 'dense_rank_final', aggfinalextra => 't', aggfinalmodify => 'w',
635635
aggmfinalmodify => 'w', aggtranstype => 'internal' },
636636

637+
# any_value
638+
{ aggfnoid => 'any_value(anyelement)', aggtransfn => 'any_value_transfn',
639+
aggcombinefn => 'any_value_transfn', aggtranstype => 'anyelement' },
640+
637641
]

‎src/include/catalog/pg_proc.dat

Copy file name to clipboardExpand all lines: src/include/catalog/pg_proc.dat
+8Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11927,4 +11927,12 @@
1192711927
prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary',
1192811928
prosrc => 'brin_minmax_multi_summary_send' },
1192911929

11930+
{ oid => '8981', descr => 'arbitrary value from among input values',
11931+
proname => 'any_value', prokind => 'a', proisstrict => 'f',
11932+
prorettype => 'anyelement', proargtypes => 'anyelement',
11933+
prosrc => 'aggregate_dummy' },
11934+
{ oid => '8982', descr => 'aggregate transition function',
11935+
proname => 'any_value_transfn', prorettype => 'anyelement',
11936+
proargtypes => 'anyelement anyelement', prosrc => 'any_value_transfn' },
11937+
1193011938
]

‎src/test/regress/expected/aggregates.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/aggregates.out
+30Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,30 @@ SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
2525
32.6666666666666667
2626
(1 row)
2727

28+
SELECT any_value(v) FROM (VALUES (1), (2), (3)) AS v (v);
29+
any_value
30+
-----------
31+
1
32+
(1 row)
33+
34+
SELECT any_value(v) FROM (VALUES (NULL)) AS v (v);
35+
any_value
36+
-----------
37+
38+
(1 row)
39+
40+
SELECT any_value(v) FROM (VALUES (NULL), (1), (2)) AS v (v);
41+
any_value
42+
-----------
43+
1
44+
(1 row)
45+
46+
SELECT any_value(v) FROM (VALUES (array['hello', 'world'])) AS v (v);
47+
any_value
48+
---------------
49+
{hello,world}
50+
(1 row)
51+
2852
-- In 7.1, avg(float4) is computed using float8 arithmetic.
2953
-- Round the result to 3 digits to avoid platform-specific results.
3054
SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
@@ -2033,6 +2057,12 @@ from (values ('a', 'b')) AS v(foo,bar);
20332057
a
20342058
(1 row)
20352059

2060+
select any_value(v) filter (where v > 2) from (values (1), (2), (3)) as v (v);
2061+
any_value
2062+
-----------
2063+
3
2064+
(1 row)
2065+
20362066
-- outer reference in FILTER (PostgreSQL extension)
20372067
select (select count(*)
20382068
from (values (1)) t0(inner_c))

‎src/test/regress/sql/aggregates.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/aggregates.sql
+7Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,11 @@ SELECT avg(four) AS avg_1 FROM onek;
2424

2525
SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
2626

27+
SELECT any_value(v) FROM (VALUES (1), (2), (3)) AS v (v);
28+
SELECT any_value(v) FROM (VALUES (NULL)) AS v (v);
29+
SELECT any_value(v) FROM (VALUES (NULL), (1), (2)) AS v (v);
30+
SELECT any_value(v) FROM (VALUES (array['hello', 'world'])) AS v (v);
31+
2732
-- In 7.1, avg(float4) is computed using float8 arithmetic.
2833
-- Round the result to 3 digits to avoid platform-specific results.
2934

@@ -810,6 +815,8 @@ having exists (select 1 from onek b where sum(distinct a.four) = b.four);
810815
select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0')
811816
from (values ('a', 'b')) AS v(foo,bar);
812817

818+
select any_value(v) filter (where v > 2) from (values (1), (2), (3)) as v (v);
819+
813820
-- outer reference in FILTER (PostgreSQL extension)
814821
select (select count(*)
815822
from (values (1)) t0(inner_c))

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.