Re: string_agg delimiter having no effect with order by

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.

regards, tom lane