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

Re: string_agg delimiter having no effect with order by

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Thom Brown <thom(at)linux(dot)com>, Greg Stark <gsstark(at)mit(dot)edu>, 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:57:22
Message-ID: AANLkTi=jAgXdG99ONy2iRvmu3sXijB6KYnmwgOKNqjHr@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
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
>

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-08-05 15:06:57
Subject: Re: BUG #5599: Vacuum fails due to index corruption issues
Previous:From: RichardDate: 2010-08-05 14:56:25
Subject: Re: Online backup cause boot failure, anyone know why?

pgsql-bugs by date

Next:From: Tom LaneDate: 2010-08-05 15:06:57
Subject: Re: BUG #5599: Vacuum fails due to index corruption issues
Previous:From: Tom LaneDate: 2010-08-05 14:41:32
Subject: Re: string_agg delimiter having no effect with order by

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