Re: xmlagg doesn't honor LIMIT?

This is how I solved it:

SELECT
xmlagg(
xmlconcat(
xmlelement(name test_element,
xmlforest(
ff.d AS a
)--xmlforest
)
)--xmlconcat
)--xmlagg

FROM (
SELECT
--xmlagg(
xmlconcat(
xmlelement(name test_element,
xmlattributes(
0 AS m
),
xmlforest(
dh.id AS i
,dh.some_value AS sv
)--xmlforest
)--test_element
) AS d
--)
FROM __pg_test_table AS dh
WHERE dh.__rel=5 LIMIT 2 --OFFSET 10;
) AS ff;

2013/11/26 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>

> Peter Kroon wrote:
> > Is anyone able to reproduce?
> > When I run the query below all 5 rows are returned instead of 2.
> > Or is this the default behaviour..
>
> > SELECT
> > xmlagg(
> [...]
> > )--xmlagg
> > FROM __pg_test_table AS dh
> > WHERE dh.__rel=5 LIMIT 2 --OFFSET 10;
>
> According to the documentation, that query should return
> exactly one row since xmlagg is an aggregate.
>
> So the LIMIT 2 won't do anything to the result.
>
> You can wrap your query in a
> SELECT count(*) FROM (SELECT ...) AS dummy;
> to see how many rows you got.
>
> Yours,
> Laurenz Albe
>