Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

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

 Sign up
Appearance settings

Commit2ddab01

Browse files
committed
Implement ANY_VALUE aggregate
SQL:2023 defines an ANY_VALUE aggregate whose purpose is to emit animplementation-dependent (i.e. non-deterministic) value from theaggregated 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 parent7e5ddf7 commit2ddab01

File tree

8 files changed

+74
-1
lines changed

8 files changed

+74
-1
lines changed

‎doc/src/sgml/func.sgml

Lines 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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -520,6 +520,7 @@ T622Trigonometric functionsYES
520520
T623General logarithm functionsYES
521521
T624Common logarithm functionsYES
522522
T625LISTAGGNO
523+
T626ANY_VALUEYESSQL:202x draft
523524
T631IN predicate with one list elementYES
524525
T641Multiple column assignmentNOonly some syntax variants supported
525526
T651SQL-schema statements in SQL routinesYES

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

Lines 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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/*yyyymmddN */
60-
#defineCATALOG_VERSION_NO202302111
60+
#defineCATALOG_VERSION_NO202302221
6161

6262
#endif

‎src/include/catalog/pg_aggregate.dat

Lines 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

Lines 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

Lines 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

Lines 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
SELECTavg(a)AS avg_32FROM aggtestWHERE 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
selectmax(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 (selectcount(*)
815822
from (values (1)) t0(inner_c))

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp