Re: string_agg delimiter having no effect with order by

On 4 August 2010 14:24, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2010/8/4 Thom Brown <thom@linux.com>:
>> On 4 August 2010 14:04, Robert Haas <robertmhaas@gmail.com> wrote:
>>> On Wed, Aug 4, 2010 at 6:03 AM, Thom Brown <thom@linux.com> wrote:
>>>> Actually, this rings a bell. =A0I think this may have been raised
>>>> before, something to do with the delimiter being accepted as one of
>>>> the order by values. =A0If this isn't really a bug, could someone
>>>> mention it in the docs somewhere?
>>>
>>> Oh, yeah. =A0I 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. =A0Perhaps we need a
>>> <note> somewhere about multi-argument aggregates.
>>>
>>
>> Yes, that works with the order clause. =A0That's really weird! =A0It loo=
ks
>> 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. =A0It'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
>

So really, should the documentation be changed from:

string_agg(expression [, delimiter ] )

to

string_agg(expression [, delimiter ] [ GROUP BY expression [, ...] ] )

?

--=20
Thom Brown
Registered Linux user: #516935