Re: string_agg delimiter having no effect with order by

2010/8/5 Tom Lane <tgl@sss.pgh.pa.us>:
> Thom Brown <thom@linux.com> writes:
>> On 5 August 2010 10:29, Greg Stark <gsstark@mit.edu> wrote:
>>> On Thu, Aug 5, 2010 at 5:18 AM, Pavel Stehule <pavel.stehule@gmail.com>=
 wrote:
>>>> The same problem can be with custom aggregates :( so this syntax isn't
>>>> too robust. We can support Oracle's syntax in future releases, where
>>>> syntax divide aggregate call and ORDER BY clause.
>>>
>>> What syntax is that?
>
>> An example I've found is:
>> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS empl=
oyees
>> FROM =C2=A0 emp
>> GROUP BY deptno;
>
> That wouldn't help this problem in the least. =C2=A0The problem is that
> novices unfamiliar with the SQL-standard aggregrate ORDER BY syntax
> may try to put the ORDER BY in the wrong place. =C2=A0Offering a different
> syntax won't stop them from doing that. =C2=A0The only way it might stop
> would be if we documented *only* the Oracle syntax and not the
> spec-compliant syntax. =C2=A0Which ain't gonna happen.
>
> [ does a bit more research ... ] =C2=A0Actually, the syntax Thom mentions
> is not Oracle-specific; it's in SQL:2008, and AFAICT it means something
> different from an aggregate ORDER BY anyway. =C2=A0Maybe Pavel had someth=
ing
> else in mind. =C2=A0But my point is still that offering a different syntax
> doesn't fix the problem unless we eliminate the mistake-prone syntax;
> which we can't because it's in the spec.
>

I though this syntax - and what I know Oracle use it for explicit
order and I found lot of sources on net, where is syntax of aggregates
like

name(parameters) [within group ( order by ... ) ]

but my knowledge of this subject is minimal, based on Oracle doc, when
I worked on string_agg function.

I agree, so different syntax doesn't remove a risks, but can decrease
some risks. SQL has lot of a possible dangerous syntaxes and everybody
can selects the most robust syntax.

But this issue can be solved a better documentation.

Regards

Pavel

> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0regards, tom lane
>