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

Commit484f046

Browse files
author
Neil Conway
committed
Implement max() and min() aggregates for array types. Patch from Koju
Iijima, reviewed by Neil Conway. Catalog version number bumped,regression tests updated.
1 parent517872c commit484f046

File tree

8 files changed

+118
-9
lines changed

8 files changed

+118
-9
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.238 2005/02/11 04:31:54 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.239 2005/02/28 03:45:20 neilc Exp $
33
PostgreSQL documentation
44
-->
55

@@ -7161,7 +7161,7 @@ SELECT NULLIF(value, '(none)') ...
71617161

71627162
<row>
71637163
<entry><function>max(<replaceable class="parameter">expression</replaceable>)</function></entry>
7164-
<entry>any numeric, string, or date/time type</entry>
7164+
<entry>anyarray,numeric, string, or date/time type</entry>
71657165
<entry>same as argument type</entry>
71667166
<entry>
71677167
maximum value of <replaceable
@@ -7172,7 +7172,7 @@ SELECT NULLIF(value, '(none)') ...
71727172

71737173
<row>
71747174
<entry><function>min(<replaceable class="parameter">expression</replaceable>)</function></entry>
7175-
<entry>any numeric, string, or date/time type</entry>
7175+
<entry>anyarray,numeric, string, or date/time type</entry>
71767176
<entry>same as argument type</entry>
71777177
<entry>
71787178
minimum value of <replaceable

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

Lines changed: 31 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/arrayfuncs.c,v 1.115 2004/12/31 22:01:21pgsql Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/arrayfuncs.c,v 1.116 2005/02/28 03:45:21neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3387,3 +3387,33 @@ makeMdArrayResult(ArrayBuildState *astate,
33873387

33883388
returnPointerGetDatum(result);
33893389
}
3390+
3391+
Datum
3392+
array_larger(PG_FUNCTION_ARGS)
3393+
{
3394+
ArrayType*v1,
3395+
*v2,
3396+
*result;
3397+
3398+
v1=PG_GETARG_ARRAYTYPE_P(0);
3399+
v2=PG_GETARG_ARRAYTYPE_P(1);
3400+
3401+
result= ((array_cmp(fcinfo)>0) ?v1 :v2);
3402+
3403+
PG_RETURN_ARRAYTYPE_P(result);
3404+
}
3405+
3406+
Datum
3407+
array_smaller(PG_FUNCTION_ARGS)
3408+
{
3409+
ArrayType*v1,
3410+
*v2,
3411+
*result;
3412+
3413+
v1=PG_GETARG_ARRAYTYPE_P(0);
3414+
v2=PG_GETARG_ARRAYTYPE_P(1);
3415+
3416+
result= ((array_cmp(fcinfo)<0) ?v1 :v2);
3417+
3418+
PG_RETURN_ARRAYTYPE_P(result);
3419+
}

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.256 2005/02/27 08:31:30 neilc Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.257 2005/02/28 03:45:21 neilc Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200502271
56+
#defineCATALOG_VERSION_NO200502281
5757

5858
#endif

‎src/include/catalog/pg_aggregate.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.48 2005/01/2817:35:53 tgl Exp $
11+
* $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.49 2005/02/2803:45:22 neilc Exp $
1212
*
1313
* NOTES
1414
* the genbki.sh script reads this file and generates .bki
@@ -110,6 +110,7 @@ DATA(insert ( 2127timestamptz_larger-1184_null_ ));
110110
DATA(insert (2128interval_larger-1186_null_ ));
111111
DATA(insert (2129text_larger-25_null_ ));
112112
DATA(insert (2130numeric_larger-1700_null_ ));
113+
DATA(insert (2050array_larger-2277_null_ ));
113114

114115
/* min */
115116
DATA(insert (2131int8smaller-20_null_ ));
@@ -128,6 +129,7 @@ DATA(insert ( 2143timestamptz_smaller -1184_null_ ));
128129
DATA(insert (2144interval_smaller-1186_null_ ));
129130
DATA(insert (2145text_smaller-25_null_ ));
130131
DATA(insert (2146numeric_smaller-1700_null_ ));
132+
DATA(insert (2051array_smaller-2277_null_ ));
131133

132134
/*
133135
* Using int8inc for count() is cheating a little, since it really only

‎src/include/catalog/pg_proc.h

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.351 2005/02/27 08:31:30 neilc Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.352 2005/02/28 03:45:22 neilc Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -1028,6 +1028,10 @@ DATA(insert OID = 394 ( string_to_array PGNSP PGUID 12 f f t f i 2 1009 "25 2
10281028
DESCR("split delimited text into text[]");
10291029
DATA(insertOID=395 (array_to_stringPGNSPPGUID12fftfi225"2277 25"_null_array_to_text-_null_ ));
10301030
DESCR("concatenate array elements, using delimiter, into text");
1031+
DATA(insertOID=515 (array_largerPGNSPPGUID12fftfi22277"2277 2277"_null_array_larger-_null_ ));
1032+
DESCR("larger of two");
1033+
DATA(insertOID=516 (array_smallerPGNSPPGUID12fftfi22277"2277 2277"_null_array_smaller-_null_ ));
1034+
DESCR("smaller of two");
10311035

10321036
DATA(insertOID=760 (smgrinPGNSPPGUID12fftfs1210"2275"_null_smgrin-_null_ ));
10331037
DESCR("I/O");
@@ -3029,6 +3033,7 @@ DATA(insert OID = 2127 ( maxPGNSP PGUID 12 t f f f i 1 1184 "1184" _null_a
30293033
DATA(insertOID=2128 (maxPGNSPPGUID12tfffi11186"1186"_null_aggregate_dummy-_null_ ));
30303034
DATA(insertOID=2129 (maxPGNSPPGUID12tfffi125"25"_null_aggregate_dummy-_null_ ));
30313035
DATA(insertOID=2130 (maxPGNSPPGUID12tfffi11700"1700"_null_aggregate_dummy-_null_ ));
3036+
DATA(insertOID=2050 (maxPGNSPPGUID12tfffi12277"2277"_null_aggregate_dummy-_null_ ));
30323037

30333038
DATA(insertOID=2131 (minPGNSPPGUID12tfffi120"20"_null_aggregate_dummy-_null_ ));
30343039
DATA(insertOID=2132 (minPGNSPPGUID12tfffi123"23"_null_aggregate_dummy-_null_ ));
@@ -3046,6 +3051,7 @@ DATA(insert OID = 2143 ( minPGNSP PGUID 12 t f f f i 1 1184 "1184" _null_a
30463051
DATA(insertOID=2144 (minPGNSPPGUID12tfffi11186"1186"_null_aggregate_dummy-_null_ ));
30473052
DATA(insertOID=2145 (minPGNSPPGUID12tfffi125"25"_null_aggregate_dummy-_null_ ));
30483053
DATA(insertOID=2146 (minPGNSPPGUID12tfffi11700"1700"_null_aggregate_dummy-_null_ ));
3054+
DATA(insertOID=2051 (minPGNSPPGUID12tfffi12277"2277"_null_aggregate_dummy-_null_ ));
30493055

30503056
DATA(insertOID=2147 (countPGNSPPGUID12tfffi120"2276"_null_aggregate_dummy-_null_ ));
30513057

‎src/include/utils/array.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
1111
* Portions Copyright (c) 1994, Regents of the University of California
1212
*
13-
* $PostgreSQL: pgsql/src/include/utils/array.h,v 1.51 2004/12/31 22:03:45 pgsql Exp $
13+
* $PostgreSQL: pgsql/src/include/utils/array.h,v 1.52 2005/02/2803:45:23 neilc Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -129,6 +129,8 @@ extern Datum array_upper(PG_FUNCTION_ARGS);
129129
externDatumarray_type_coerce(PG_FUNCTION_ARGS);
130130
externDatumarray_type_length_coerce(PG_FUNCTION_ARGS);
131131
externDatumarray_length_coerce(PG_FUNCTION_ARGS);
132+
externDatumarray_larger(PG_FUNCTION_ARGS);
133+
externDatumarray_smaller(PG_FUNCTION_ARGS);
132134

133135
externDatumarray_ref(ArrayType*array,intnSubscripts,int*indx,
134136
intarraylen,intelmlen,boolelmbyval,charelmalign,

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

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -483,3 +483,47 @@ select '{
483483
(1 row)
484484

485485
-- all of the above should be accepted
486+
-- tests for array aggregates
487+
CREATE TEMP TABLE arraggtest ( f1 INT[], f2 TEXT[][], f3 FLOAT[]);
488+
INSERT INTO arraggtest (f1, f2, f3) VALUES
489+
('{1,2,3,4}','{{grey,red},{blue,blue}}','{1.6, 0.0}');
490+
INSERT INTO arraggtest (f1, f2, f3) VALUES
491+
('{1,2,3}','{{grey,red},{grey,blue}}','{1.6}');
492+
SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
493+
max | min | max | min | max | min
494+
-----------+---------+--------------------------+--------------------------+---------+-------
495+
{1,2,3,4} | {1,2,3} | {{grey,red},{grey,blue}} | {{grey,red},{blue,blue}} | {1.6,0} | {1.6}
496+
(1 row)
497+
498+
INSERT INTO arraggtest (f1, f2, f3) VALUES
499+
('{3,3,2,4,5,6}','{{white,yellow},{pink,orange}}','{2.1,3.3,1.8,1.7,1.6}');
500+
SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
501+
max | min | max | min | max | min
502+
---------------+---------+--------------------------------+--------------------------+-----------------------+-------
503+
{3,3,2,4,5,6} | {1,2,3} | {{white,yellow},{pink,orange}} | {{grey,red},{blue,blue}} | {2.1,3.3,1.8,1.7,1.6} | {1.6}
504+
(1 row)
505+
506+
INSERT INTO arraggtest (f1, f2, f3) VALUES
507+
('{2}','{{black,red},{green,orange}}','{1.6,2.2,2.6,0.4}');
508+
SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
509+
max | min | max | min | max | min
510+
---------------+---------+--------------------------------+------------------------------+-----------------------+-------
511+
{3,3,2,4,5,6} | {1,2,3} | {{white,yellow},{pink,orange}} | {{black,red},{green,orange}} | {2.1,3.3,1.8,1.7,1.6} | {1.6}
512+
(1 row)
513+
514+
INSERT INTO arraggtest (f1, f2, f3) VALUES
515+
('{4,2,6,7,8,1}','{{red},{black},{purple},{blue},{blue}}',NULL);
516+
SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
517+
max | min | max | min | max | min
518+
---------------+---------+--------------------------------+------------------------------+-----------------------+-------
519+
{4,2,6,7,8,1} | {1,2,3} | {{white,yellow},{pink,orange}} | {{black,red},{green,orange}} | {2.1,3.3,1.8,1.7,1.6} | {1.6}
520+
(1 row)
521+
522+
INSERT INTO arraggtest (f1, f2, f3) VALUES
523+
('{}','{{pink,white,blue,red,grey,orange}}','{2.1,1.87,1.4,2.2}');
524+
SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
525+
max | min | max | min | max | min
526+
---------------+-----+--------------------------------+------------------------------+-----------------------+-------
527+
{4,2,6,7,8,1} | {} | {{white,yellow},{pink,orange}} | {{black,red},{green,orange}} | {2.1,3.3,1.8,1.7,1.6} | {1.6}
528+
(1 row)
529+

‎src/test/regress/sql/arrays.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -217,3 +217,28 @@ select '{
217217
@ 1 hour @ 42 minutes @ 20 seconds
218218
}'::interval[];
219219
-- all of the above should be accepted
220+
221+
-- tests for array aggregates
222+
CREATE TEMP TABLE arraggtest ( f1INT[], f2TEXT[][], f3 FLOAT[]);
223+
224+
INSERT INTO arraggtest (f1, f2, f3)VALUES
225+
('{1,2,3,4}','{{grey,red},{blue,blue}}','{1.6, 0.0}');
226+
INSERT INTO arraggtest (f1, f2, f3)VALUES
227+
('{1,2,3}','{{grey,red},{grey,blue}}','{1.6}');
228+
SELECTmax(f1),min(f1),max(f2),min(f2),max(f3),min(f3)FROM arraggtest;
229+
230+
INSERT INTO arraggtest (f1, f2, f3)VALUES
231+
('{3,3,2,4,5,6}','{{white,yellow},{pink,orange}}','{2.1,3.3,1.8,1.7,1.6}');
232+
SELECTmax(f1),min(f1),max(f2),min(f2),max(f3),min(f3)FROM arraggtest;
233+
234+
INSERT INTO arraggtest (f1, f2, f3)VALUES
235+
('{2}','{{black,red},{green,orange}}','{1.6,2.2,2.6,0.4}');
236+
SELECTmax(f1),min(f1),max(f2),min(f2),max(f3),min(f3)FROM arraggtest;
237+
238+
INSERT INTO arraggtest (f1, f2, f3)VALUES
239+
('{4,2,6,7,8,1}','{{red},{black},{purple},{blue},{blue}}',NULL);
240+
SELECTmax(f1),min(f1),max(f2),min(f2),max(f3),min(f3)FROM arraggtest;
241+
242+
INSERT INTO arraggtest (f1, f2, f3)VALUES
243+
('{}','{{pink,white,blue,red,grey,orange}}','{2.1,1.87,1.4,2.2}');
244+
SELECTmax(f1),min(f1),max(f2),min(f2),max(f3),min(f3)FROM arraggtest;

0 commit comments

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