Re: Bug with ordering aggregates?

On 18 May 2010 16:37, Stephen Frost <sfrost@snowman.net> wrote:
> Greetings,
>
>  This doesn't seem right to me:
>
> postgres=# select
> postgres-# string_agg(column1::text order by column1 asc,',')
> postgres-# from (values (3),(4),(1),(2)) a;
>  string_agg
> ------------
>  1234
> (1 row)
>
>  I'm thinking we should toss a syntax error here and force the 'order
>  by' to be at the end of any arguments to the aggregate.
>  Alternatively, we should actually make this work like this one does:
>
> postgres=# select
> postgres-# string_agg(column1::text,',' order by column1 asc)
> postgres-# from (values (3),(4),(1),(2)) a;
>  string_agg
> ------------
>  1,2,3,4
> (1 row)
>

I find that 2nd example confusing.  It suggests the delimiter is being
ordered as the order by clause appears in its parameter.  But I can
see why the first one is returning the wrong result.  The order by
clause conflicts with the delimiter parameter as obviously the order
by clause prevents you specifying a 2nd parameter in the aggregate
function.  The delimiter would either need to be the first parameter,
or the order by clause would require a way to terminate it's order by
list.

Thom