<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.457 2008/11/12 13:09:27 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.458 2008/11/13 15:59:50 petere Exp $ -->

<chapter id="functions">

<title>Functions and Operators</title>

</para>

</sect3>

<sect3>

<sect3 id="functions-xml-xmlagg">

<title><literal>xmlagg</literal></title>

<indexterm>

<para>

The function <function>xmlagg</function> is, unlike the other

functionsbelow, an aggregate function. It concatenates the

functionsdescribed here, an aggregate function. It concatenates the

input values to the aggregate function call,

like <function>xmlconcat</function> does.

See <xref linkend="functions-aggregate"> forgeneral information

See <xref linkend="functions-aggregate"> foradditional information

about aggregate functions.

</para>

</para>

<para>

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:

<screen><![CDATA[

SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;

xmlagg

----------------------

<bar/><foo>abc</foo>

]]></screen>

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 (<literal>xmlagg(expr ORDER BY expr, expr,

...</literal>).

Again, see <xref linkend="functions-aggregate"> for additional

information.

</para>

</sect3>

</tbody>

</tgroup>

</table>

<para>

See also <xref linkend="functions-aggregate"> about the aggregate

function <function>array_agg</function> for use with arrays.

</para>

</sect1>

<sect1 id="functions-aggregate">

</thead>

<tbody>

<row>

<entry>

<indexterm>

<primary>array_agg</primary>

</indexterm>

<function>array_agg(<replaceable class="parameter">expression</replaceable>)</function>

</entry>

<entry>

any

</entry>

<entry>

array of the argument type

</entry>

<entry>input values concatenated into an array</entry>

</row>

<row>

<entry>

<indexterm>

</entry>

<entry>sum of <replaceable class="parameter">expression</replaceable> across all input values</entry>

</row>

<row>

<entry>

<indexterm>

<primary>xmlagg</primary>

</indexterm>

<function>xmlagg(<replaceable class="parameter">expression</replaceable>)</function>

</entry>

<entry>

<type>xml</type>

</entry>

<entry>

<type>xml</type>

</entry>

<entry>concatenation of XML values (see also <xref linkend="functions-xml-xmlagg">)</entry>

</row>

</tbody>

</tgroup>

</table>

It should be noted that except for <function>count</function>,

these functions return a null value when no rows are selected. In

particular, <function>sum</function> of no rows returns null, not

zero as one might expect. The <function>coalesce</function> function can be

used to substitute zero for null when necessary.

zero as one might expect, and <function>array_agg</function>

returns null rather than an empty array when there are no input

rows. The <function>coalesce</function> function can be used to

substitute zero or an empty array for null when necessary.

</para>

<note>

</para>

</note>

<para>

The aggregate functions <function>array_agg</function>

and <function>xmlagg</function>, 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:

<screen><![CDATA[

SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;

]]></screen>

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 (<literal>xmlagg(expr ORDER BY expr, expr,

...</literal>).

</para>

<para>

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

S095Array constructors by queryYES

S096Optional array boundsYES

S097Array element assignmentNO

S098ARRAY_AGGNO

S098ARRAY_AGGNOORDER BY clause not supported

S111ONLY in query expressionsYES

S151Type predicateNO

S161Subtype treatmentNO

returnconstruct_md_array(dvalues,NULL,ndims,dims,lbs,element_type,

typlen,typbyval,typalign);

}

array_agg_transfn(PG_FUNCTION_ARGS)

{

Oidarg1_typeid=get_fn_expr_argtype(fcinfo->flinfo,1);

if (arg1_typeid==InvalidOid)

ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),

errmsg("could not determine input data type")));

Assert(fcinfo->context&&IsA(fcinfo->context,AggState));

PG_RETURN_POINTER(accumArrayResult(PG_ARGISNULL(0) ?NULL : (ArrayBuildState*)PG_GETARG_POINTER(0),

PG_ARGISNULL(1) ? (Datum)0 :PG_GETARG_DATUM(1),

PG_ARGISNULL(1),

arg1_typeid,

((AggState*)fcinfo->context)->aggcontext));

}

array_agg_finalfn(PG_FUNCTION_ARGS)

{

Assert(fcinfo->context&&IsA(fcinfo->context,AggState));

if (PG_ARGISNULL(0))

PG_RETURN_NULL();/* returns null iff no input values */

PG_RETURN_ARRAYTYPE_P(makeArrayResult((ArrayBuildState*)PG_GETARG_POINTER(0),CurrentMemoryContext));

}

DATA(insert (2901xmlconcat2-0142_null_ ));

DATA(insert (2335array_agg_transfnarray_agg_finalfn02281_null_ ));

DESCR("array constructor with value");

DATA(insertOID=1286 (array_fillPGNSPPGUID12100ffffi32277"2283 1007 1007"_null__null__null_array_fill_with_lower_bounds_null__null__null_ ));

DESCR("array constructor with value");

DATA(insertOID=2333 (array_agg_transfnPGNSPPGUID12100ffffi22281"2281 2283"_null__null__null_array_agg_transfn_null__null__null_ ));

DESCR("array_agg transition function");

DATA(insertOID=2334 (array_agg_finalfnPGNSPPGUID12100ffffi12277"2281"_null__null__null_array_agg_finalfn_null__null__null_ ));

DESCR("array_agg final function");

DATA(insertOID=2335 (array_aggPGNSPPGUID12100tfffi12277"2283"_null__null__null_aggregate_dummy_null__null__null_ ));

DESCR("concatenate aggregate input into an array");

DATA(insertOID=760 (smgrinPGNSPPGUID12100fftfs1210"2275"_null__null__null_smgrin_null__null__null_ ));

DESCR("I/O");

DATA(insertOID=761 (smgroutPGNSPPGUID12100fftfs12275"210"_null__null__null_smgrout_null__null__null_ ));

Datumelement,

intndims);

externDatumarray_agg_transfn(PG_FUNCTION_ARGS);

externDatumarray_agg_finalfn(PG_FUNCTION_ARGS);

{foo,new_word} | 2 | {bar,foo} | 2

(3 rows)

select array_agg(unique1) from tenk1 where unique1 < 15;

array_agg

--------------------------------------

{4,2,1,6,14,9,8,5,3,13,12,11,7,10,0}

(1 row)

select array_agg(ten) from tenk1 where unique1 < 15;

array_agg

---------------------------------

{4,2,1,6,4,9,8,5,3,3,2,1,7,0,0}

(1 row)

select array_agg(nullif(ten, 4)) from tenk1 where unique1 < 15;

array_agg

---------------------------------------

{NULL,2,1,6,NULL,9,8,5,3,3,2,1,7,0,0}

(1 row)

select cardinality(array_agg(unique1)) from tenk1 where unique1 < 15;

cardinality

-------------

15

(1 row)

select array_agg(unique1) from (select * from tenk1 order by unique1 asc) as tab where unique1 < 15;

array_agg

--------------------------------------

{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}

(1 row)

select array_agg(unique1) from tenk1 where unique1 < -15;

array_agg

-----------

(1 row)

select cardinality(array[1,2,3]);

select cardinality(array[[1,2,3], [4,5,6]]);

select c, cardinality(c), d, cardinality(d)from arrtest;

select array_agg(unique1)from tenk1where unique1<15;

select array_agg(ten)from tenk1where unique1<15;

select array_agg(nullif(ten,4))from tenk1where unique1<15;

select cardinality(array_agg(unique1))from tenk1where unique1<15;

select array_agg(unique1)from (select*from tenk1order by unique1asc)as tabwhere unique1<15;

select array_agg(unique1)from tenk1where unique1<-15;