From 3379fae6de5994b242cedfa48cf613ecfee3db24 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 13 Nov 2008 15:59:51 +0000 Subject: 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 similar semantics and issues. best of Robert Haas, Jeff Davis, Peter Eisentraut --- doc/src/sgml/func.sgml | 85 ++++++++++++++++++++++++++++++++++++++++---------- 1 file changed, 69 insertions(+), 16 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 85403e2c9f7..20443f2b288 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -8353,7 +8353,7 @@ SELECT xmlroot(xmlparse(document 'abc'), - + <literal>xmlagg</literal> @@ -8366,10 +8366,10 @@ SELECT xmlroot(xmlparse(document 'abc'), The function xmlagg is, unlike the other - functions below, an aggregate function. It concatenates the + functions described here, an aggregate function. It concatenates the input values to the aggregate function call, like xmlconcat does. - See for general information + See for additional information about aggregate functions. @@ -8387,11 +8387,9 @@ SELECT xmlagg(x) FROM test; - Note that in the current implementation, the order of the - concatenation is in principle undefined. Making the input values - to be sorted in some other way will usually work, however. For - instance, in the above example, one could influence the order - like so: + The influence the order of the concatenation, something like the + following approach to sort the input values can be used: + abc ]]> - But this approach is not guaranteed to work in all situations and - in all versions of PostgreSQL. A future version of PostgreSQL - will probably provide an additional feature to control the order - in a proper way (xmlagg(expr ORDER BY expr, expr, - ...). + Again, see for additional + information. @@ -9490,6 +9485,11 @@ SELECT NULLIF(value, '(none)') ... + + + See also about the aggregate + function array_agg for use with arrays. + @@ -9526,6 +9526,22 @@ SELECT NULLIF(value, '(none)') ... + + + + array_agg + + array_agg(expression) + + + any + + + array of the argument type + + input values concatenated into an array + + @@ -9684,6 +9700,22 @@ SELECT NULLIF(value, '(none)') ... sum of expression across all input values + + + + + xmlagg + + xmlagg(expression) + + + xml + + + xml + + concatenation of XML values (see also ) + @@ -9692,8 +9724,10 @@ SELECT NULLIF(value, '(none)') ... It should be noted that except for count, these functions return a null value when no rows are selected. In particular, sum of no rows returns null, not - zero as one might expect. The coalesce function can be - used to substitute zero for null when necessary. + zero as one might expect, and array_agg + returns null rather than an empty array when there are no input + rows. The coalesce function can be used to + substitute zero or an empty array for null when necessary. @@ -9733,6 +9767,25 @@ SELECT count(*) FROM sometable; + + The aggregate functions array_agg + and xmlagg, as well as similar user-defined + aggregate functions, produce meaningfully different result values + depending on the order of the input values. In the current + implementation, the order of the concatenation is in principle + undefined. Making the input values to be sorted in some other way + will usually work, however. For example: + + + + But this approach is not guaranteed to work in all situations, and + it is not strictly SQL-conforming. A future version of PostgreSQL + might provide an additional feature to control the order in a + better-defined way (xmlagg(expr ORDER BY expr, expr, + ...). + shows -- cgit v1.2.3