Re: Order By for aggregate functions (Simulating Group_concat)

Charlotte Pollock <c.pollock@bangor.ac.uk> writes:
> I'm trying to create a aggregate function similar 9but not identical) to
> mysql's group_concat.

> What I want to be able to do is pass and order by field to the aggregate so
> I can be certain I get the list of strings in the correct order.

The way this is usually done in PG is to order the data before it gets
to the aggregate function.  For the ungrouped case this is easy:

    SELECT my_concat(foo) FROM
      (SELECT foo FROM ... ORDER BY something) ss;

If you're trying to aggregate within groups it's a bit trickier.  The
secret is that the ordering of the inner sub-select has to match the
outer GROUP BY:

    SELECT my_concat(foo), bar FROM
      (SELECT foo,bar FROM ... ORDER BY bar, something) ss
    GROUP BY bar;

In some cases it'll still work with just ORDER BY something, but that
depends on which plan type the planner happens to choose, so it's not
reliable to leave off the ORDER BY bar.

This requires a fairly recent PG ... I think we fixed the planner to
make this work properly in 7.4.

            regards, tom lane