Re: string_agg delimiter having no effect with order by

2010/8/5 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Thom Brown <thom(at)linux(dot)com> writes:
>> On 5 August 2010 10:29, Greg Stark <gsstark(at)mit(dot)edu> wrote:
>>> On Thu, Aug 5, 2010 at 5:18 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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 employees
>> FROM   emp
>> GROUP BY deptno;
>
> That wouldn't help this problem in the least.  The problem is that
> novices unfamiliar with the SQL-standard aggregrate ORDER BY syntax
> may try to put the ORDER BY in the wrong place.  Offering a different
> syntax won't stop them from doing that.  The only way it might stop
> would be if we documented *only* the Oracle syntax and not the
> spec-compliant syntax.  Which ain't gonna happen.
>
> [ does a bit more research ... ]  Actually, 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.  Maybe Pavel had something
> else in mind.  But 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

>                        regards, tom lane
>