Skip site navigation (1) Skip section navigation (2)

Re: string_agg delimiter having no effect with order by

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thom Brown <thom(at)linux(dot)com>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, Alex Hunsaker <badalex(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: string_agg delimiter having no effect with order by
Date: 2010-08-05 14:41:32
Message-ID: 1413.1281019292@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Boszormenyi ZoltanDate: 2010-08-05 14:41:38
Subject: Re: ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Previous:From: Robert HaasDate: 2010-08-05 14:38:37
Subject: Re: Re: Re: [HACKERS] Online backup cause boot failure,anyone know why?

pgsql-bugs by date

Next:From: Pavel StehuleDate: 2010-08-05 14:57:22
Subject: Re: string_agg delimiter having no effect with order by
Previous:From: ChrisDate: 2010-08-05 14:27:41
Subject: BUG #5602: Recovering from Hot-Standby file backup leads to the currupted indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group