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