Re: string_agg delimiter having no effect with order by

2010/8/4 Thom Brown <thom@linux.com>:
> 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. =C2=A0I think this may have been raised
>>>>> before, something to do with the delimiter being accepted as one of
>>>>> the order by values. =C2=A0If this isn't really a bug, could someone
>>>>> mention it in the docs somewhere?
>>>>
>>>> Oh, yeah. =C2=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. =C2=A0Perhaps we need a
>>>> <note> somewhere about multi-argument aggregates.
>>>>
>>>
>>> Yes, that works with the order clause. =C2=A0That's really weird! =C2=
=A0It 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. =C2=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 [, ...] ] )

This syntax is available for all aggregate functions - this feature
isn't specific for string_agg

but there can be more descriptive example.

Regards

Pavel
>
> ?
>
> --
> Thom Brown
> Registered Linux user: #516935
>