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

Commit3379fae

Browse files
committed
array_agg aggregate function, as per SQL:2008, but without ORDER BY clause
Rearrange the documentation a bit now that array_agg and xmlagg have similarsemantics and issues.best of Robert Haas, Jeff Davis, Peter Eisentraut
1 parent69a0e2f commit3379fae

File tree

9 files changed

+164
-23
lines changed

9 files changed

+164
-23
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 69 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.457 2008/11/12 13:09:27 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.458 2008/11/13 15:59:50 petere Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -8353,7 +8353,7 @@ SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>abc</content>'),
83538353
</para>
83548354
</sect3>
83558355

8356-
<sect3>
8356+
<sect3 id="functions-xml-xmlagg">
83578357
<title><literal>xmlagg</literal></title>
83588358

83598359
<indexterm>
@@ -8366,10 +8366,10 @@ SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>abc</content>'),
83668366

83678367
<para>
83688368
The function <function>xmlagg</function> is, unlike the other
8369-
functionsbelow, an aggregate function. It concatenates the
8369+
functionsdescribed here, an aggregate function. It concatenates the
83708370
input values to the aggregate function call,
83718371
like <function>xmlconcat</function> does.
8372-
See <xref linkend="functions-aggregate"> forgeneral information
8372+
See <xref linkend="functions-aggregate"> foradditional information
83738373
about aggregate functions.
83748374
</para>
83758375

@@ -8387,23 +8387,18 @@ SELECT xmlagg(x) FROM test;
83878387
</para>
83888388

83898389
<para>
8390-
Note that in the current implementation, the order of the
8391-
concatenation is in principle undefined. Making the input values
8392-
to be sorted in some other way will usually work, however. For
8393-
instance, in the above example, one could influence the order
8394-
like so:
8390+
The influence the order of the concatenation, something like the
8391+
following approach to sort the input values can be used:
8392+
83958393
<screen><![CDATA[
83968394
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
83978395
xmlagg
83988396
----------------------
83998397
<bar/><foo>abc</foo>
84008398
]]></screen>
84018399

8402-
But this approach is not guaranteed to work in all situations and
8403-
in all versions of PostgreSQL. A future version of PostgreSQL
8404-
will probably provide an additional feature to control the order
8405-
in a proper way (<literal>xmlagg(expr ORDER BY expr, expr,
8406-
...</literal>).
8400+
Again, see <xref linkend="functions-aggregate"> for additional
8401+
information.
84078402
</para>
84088403
</sect3>
84098404

@@ -9490,6 +9485,11 @@ SELECT NULLIF(value, '(none)') ...
94909485
</tbody>
94919486
</tgroup>
94929487
</table>
9488+
9489+
<para>
9490+
See also <xref linkend="functions-aggregate"> about the aggregate
9491+
function <function>array_agg</function> for use with arrays.
9492+
</para>
94939493
</sect1>
94949494

94959495
<sect1 id="functions-aggregate">
@@ -9526,6 +9526,22 @@ SELECT NULLIF(value, '(none)') ...
95269526
</thead>
95279527

95289528
<tbody>
9529+
<row>
9530+
<entry>
9531+
<indexterm>
9532+
<primary>array_agg</primary>
9533+
</indexterm>
9534+
<function>array_agg(<replaceable class="parameter">expression</replaceable>)</function>
9535+
</entry>
9536+
<entry>
9537+
any
9538+
</entry>
9539+
<entry>
9540+
array of the argument type
9541+
</entry>
9542+
<entry>input values concatenated into an array</entry>
9543+
</row>
9544+
95299545
<row>
95309546
<entry>
95319547
<indexterm>
@@ -9684,6 +9700,22 @@ SELECT NULLIF(value, '(none)') ...
96849700
</entry>
96859701
<entry>sum of <replaceable class="parameter">expression</replaceable> across all input values</entry>
96869702
</row>
9703+
9704+
<row>
9705+
<entry>
9706+
<indexterm>
9707+
<primary>xmlagg</primary>
9708+
</indexterm>
9709+
<function>xmlagg(<replaceable class="parameter">expression</replaceable>)</function>
9710+
</entry>
9711+
<entry>
9712+
<type>xml</type>
9713+
</entry>
9714+
<entry>
9715+
<type>xml</type>
9716+
</entry>
9717+
<entry>concatenation of XML values (see also <xref linkend="functions-xml-xmlagg">)</entry>
9718+
</row>
96879719
</tbody>
96889720
</tgroup>
96899721
</table>
@@ -9692,8 +9724,10 @@ SELECT NULLIF(value, '(none)') ...
96929724
It should be noted that except for <function>count</function>,
96939725
these functions return a null value when no rows are selected. In
96949726
particular, <function>sum</function> of no rows returns null, not
9695-
zero as one might expect. The <function>coalesce</function> function can be
9696-
used to substitute zero for null when necessary.
9727+
zero as one might expect, and <function>array_agg</function>
9728+
returns null rather than an empty array when there are no input
9729+
rows. The <function>coalesce</function> function can be used to
9730+
substitute zero or an empty array for null when necessary.
96979731
</para>
96989732

96999733
<note>
@@ -9733,6 +9767,25 @@ SELECT count(*) FROM sometable;
97339767
</para>
97349768
</note>
97359769

9770+
<para>
9771+
The aggregate functions <function>array_agg</function>
9772+
and <function>xmlagg</function>, as well as similar user-defined
9773+
aggregate functions, produce meaningfully different result values
9774+
depending on the order of the input values. In the current
9775+
implementation, the order of the concatenation is in principle
9776+
undefined. Making the input values to be sorted in some other way
9777+
will usually work, however. For example:
9778+
9779+
<screen><![CDATA[
9780+
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
9781+
]]></screen>
9782+
9783+
But this approach is not guaranteed to work in all situations, and
9784+
it is not strictly SQL-conforming. A future version of PostgreSQL
9785+
might provide an additional feature to control the order in a
9786+
better-defined way (<literal>xmlagg(expr ORDER BY expr, expr,
9787+
...</literal>).
9788+
</para>
97369789

97379790
<para>
97389791
<xref linkend="functions-aggregate-statistics-table"> shows

‎src/backend/catalog/sql_features.txt

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -350,7 +350,7 @@ S094Arrays of reference typesNO
350350
S095Array constructors by queryYES
351351
S096Optional array boundsYES
352352
S097Array element assignmentNO
353-
S098ARRAY_AGGNO
353+
S098ARRAY_AGGNOORDER BY clause not supported
354354
S111ONLY in query expressionsYES
355355
S151Type predicateNO
356356
S161Subtype treatmentNO

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

Lines changed: 33 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,12 +6,13 @@
66
* Copyright (c) 2003-2008, PostgreSQL Global Development Group
77
*
88
* IDENTIFICATION
9-
* $PostgreSQL: pgsql/src/backend/utils/adt/array_userfuncs.c,v 1.23 2008/01/01 19:45:52 momjian Exp $
9+
* $PostgreSQL: pgsql/src/backend/utils/adt/array_userfuncs.c,v 1.24 2008/11/13 15:59:50 petere Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
1313
#include"postgres.h"
1414

15+
#include"nodes/execnodes.h"
1516
#include"utils/array.h"
1617
#include"utils/builtins.h"
1718
#include"utils/lsyscache.h"
@@ -465,3 +466,34 @@ create_singleton_array(FunctionCallInfo fcinfo,
465466
returnconstruct_md_array(dvalues,NULL,ndims,dims,lbs,element_type,
466467
typlen,typbyval,typalign);
467468
}
469+
470+
Datum
471+
array_agg_transfn(PG_FUNCTION_ARGS)
472+
{
473+
Oidarg1_typeid=get_fn_expr_argtype(fcinfo->flinfo,1);
474+
475+
if (arg1_typeid==InvalidOid)
476+
ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
477+
errmsg("could not determine input data type")));
478+
479+
/* cannot be called directly because of internal-type argument */
480+
Assert(fcinfo->context&&IsA(fcinfo->context,AggState));
481+
482+
PG_RETURN_POINTER(accumArrayResult(PG_ARGISNULL(0) ?NULL : (ArrayBuildState*)PG_GETARG_POINTER(0),
483+
PG_ARGISNULL(1) ? (Datum)0 :PG_GETARG_DATUM(1),
484+
PG_ARGISNULL(1),
485+
arg1_typeid,
486+
((AggState*)fcinfo->context)->aggcontext));
487+
}
488+
489+
Datum
490+
array_agg_finalfn(PG_FUNCTION_ARGS)
491+
{
492+
/* cannot be called directly because of internal-type argument */
493+
Assert(fcinfo->context&&IsA(fcinfo->context,AggState));
494+
495+
if (PG_ARGISNULL(0))
496+
PG_RETURN_NULL();/* returns null iff no input values */
497+
498+
PG_RETURN_ARRAYTYPE_P(makeArrayResult((ArrayBuildState*)PG_GETARG_POINTER(0),CurrentMemoryContext));
499+
}

‎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-2008, 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.504 2008/11/12 13:09:27 petere Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.505 2008/11/13 15:59:50 petere Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200811121
56+
#defineCATALOG_VERSION_NO200811131
5757

5858
#endif

‎src/include/catalog/pg_aggregate.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2008, 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.66 2008/03/27 03:57:34 tgl Exp $
11+
* $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.67 2008/11/13 15:59:50 petere Exp $
1212
*
1313
* NOTES
1414
* the genbki.sh script reads this file and generates .bki
@@ -220,6 +220,9 @@ DATA(insert ( 2243 bitor -01560_null_ ));
220220
/* xml */
221221
DATA(insert (2901xmlconcat2-0142_null_ ));
222222

223+
/* array */
224+
DATA(insert (2335array_agg_transfnarray_agg_finalfn02281_null_ ));
225+
223226
/*
224227
* prototypes for functions in pg_aggregate.c
225228
*/

‎src/include/catalog/pg_proc.h

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2008, 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.526 2008/11/12 13:38:04 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.527 2008/11/13 15:59:50 petere Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -1022,6 +1022,13 @@ DATA(insert OID = 1193 ( array_fill PGNSP PGUID 12 1 0 0 f f f f i 2 2277 "2283
10221022
DESCR("array constructor with value");
10231023
DATA(insertOID=1286 (array_fillPGNSPPGUID12100ffffi32277"2283 1007 1007"_null__null__null_array_fill_with_lower_bounds_null__null__null_ ));
10241024
DESCR("array constructor with value");
1025+
DATA(insertOID=2333 (array_agg_transfnPGNSPPGUID12100ffffi22281"2281 2283"_null__null__null_array_agg_transfn_null__null__null_ ));
1026+
DESCR("array_agg transition function");
1027+
DATA(insertOID=2334 (array_agg_finalfnPGNSPPGUID12100ffffi12277"2281"_null__null__null_array_agg_finalfn_null__null__null_ ));
1028+
DESCR("array_agg final function");
1029+
DATA(insertOID=2335 (array_aggPGNSPPGUID12100tfffi12277"2283"_null__null__null_aggregate_dummy_null__null__null_ ));
1030+
DESCR("concatenate aggregate input into an array");
1031+
10251032
DATA(insertOID=760 (smgrinPGNSPPGUID12100fftfs1210"2275"_null__null__null_smgrin_null__null__null_ ));
10261033
DESCR("I/O");
10271034
DATA(insertOID=761 (smgroutPGNSPPGUID12100fftfs12275"210"_null__null__null_smgrout_null__null__null_ ));

‎src/include/utils/array.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -49,7 +49,7 @@
4949
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
5050
* Portions Copyright (c) 1994, Regents of the University of California
5151
*
52-
* $PostgreSQL: pgsql/src/include/utils/array.h,v 1.70 2008/11/12 13:09:28 petere Exp $
52+
* $PostgreSQL: pgsql/src/include/utils/array.h,v 1.71 2008/11/13 15:59:50 petere Exp $
5353
*
5454
*-------------------------------------------------------------------------
5555
*/
@@ -275,4 +275,7 @@ extern ArrayType *create_singleton_array(FunctionCallInfo fcinfo,
275275
Datumelement,
276276
intndims);
277277

278+
externDatumarray_agg_transfn(PG_FUNCTION_ARGS);
279+
externDatumarray_agg_finalfn(PG_FUNCTION_ARGS);
280+
278281
#endif/* ARRAY_H */

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

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1125,3 +1125,39 @@ select c, cardinality(c), d, cardinality(d) from arrtest;
11251125
{foo,new_word} | 2 | {bar,foo} | 2
11261126
(3 rows)
11271127

1128+
select array_agg(unique1) from tenk1 where unique1 < 15;
1129+
array_agg
1130+
--------------------------------------
1131+
{4,2,1,6,14,9,8,5,3,13,12,11,7,10,0}
1132+
(1 row)
1133+
1134+
select array_agg(ten) from tenk1 where unique1 < 15;
1135+
array_agg
1136+
---------------------------------
1137+
{4,2,1,6,4,9,8,5,3,3,2,1,7,0,0}
1138+
(1 row)
1139+
1140+
select array_agg(nullif(ten, 4)) from tenk1 where unique1 < 15;
1141+
array_agg
1142+
---------------------------------------
1143+
{NULL,2,1,6,NULL,9,8,5,3,3,2,1,7,0,0}
1144+
(1 row)
1145+
1146+
select cardinality(array_agg(unique1)) from tenk1 where unique1 < 15;
1147+
cardinality
1148+
-------------
1149+
15
1150+
(1 row)
1151+
1152+
select array_agg(unique1) from (select * from tenk1 order by unique1 asc) as tab where unique1 < 15;
1153+
array_agg
1154+
--------------------------------------
1155+
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
1156+
(1 row)
1157+
1158+
select array_agg(unique1) from tenk1 where unique1 < -15;
1159+
array_agg
1160+
-----------
1161+
1162+
(1 row)
1163+

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -395,3 +395,10 @@ select array_length(array[[1,2,3], [4,5,6]], 3);
395395
select cardinality(array[1,2,3]);
396396
select cardinality(array[[1,2,3], [4,5,6]]);
397397
select c, cardinality(c), d, cardinality(d)from arrtest;
398+
399+
select array_agg(unique1)from tenk1where unique1<15;
400+
select array_agg(ten)from tenk1where unique1<15;
401+
select array_agg(nullif(ten,4))from tenk1where unique1<15;
402+
select cardinality(array_agg(unique1))from tenk1where unique1<15;
403+
select array_agg(unique1)from (select*from tenk1order by unique1asc)as tabwhere unique1<15;
404+
select array_agg(unique1)from tenk1where unique1<-15;

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp