Re: string_agg delimiter having no effect with order by

2010/8/4 Thom Brown <thom(at)linux(dot)com>:
> On 4 August 2010 14:04, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Wed, Aug 4, 2010 at 6:03 AM, Thom Brown <thom(at)linux(dot)com> wrote:
>>> Actually, this rings a bell.  I think this may have been raised
>>> before, something to do with the delimiter being accepted as one of
>>> the order by values.  If this isn't really a bug, could someone
>>> mention it in the docs somewhere?
>>
>> Oh, yeah.  I guess you need this:
>>
>> select thing, string_agg(stuff, ',' order by stuff) from agg_test
>> group by thing;
>>
>> Rather than this:
>>
>> select thing, string_agg(stuff order by stuff, ',') from agg_test
>> group by thing;
>>
>> It's all kinds of not obvious to me what the second one is supposed to
>> mean, but I remember this was discussed before.  Perhaps we need a
>> <note> somewhere about multi-argument aggregates.
>>
>
> Yes, that works with the order clause.  That's really weird!  It looks
> like part of the delimiter parameter, and that's undocumented, or at
> least impossible to gleen from the documentation.
>
> This should be clarified as it looks like having ORDER BY *or* a
> delimiter is supported, but not both.  It's horribly unintuitive!
> This is one of the very few cases where MySQL's version actually makes
> more sense.

this goes from ANSI SQL standard :( - I agree, this isn't intuitive
and pg can do better diagnostic now. But it has a sense. ORDER BY
hasn't sense for one parameter - only for complete function, so is
wrong to write ORDER BY over a some interesting parameter

Regards

Pavel Stehule

>
> Thom
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>