Re: BUG #15143: Window Functions – Paranthese not allowed before OVER term

On 5 April 2018 at 14:40, David G. Johnston <david.g.johnston@gmail.com> wrote:
> On Wed, Apr 4, 2018 at 7:32 PM, David Rowley <david.rowley@2ndquadrant.com>
> wrote:
>>
>> > Working as documented:
>> >
>> > "A window function call always contains an OVER clause directly
>> > following
>> > the window function's name and argument(s)"
>>
>> Yeah, how else would the window function know which window clause it
>> belongs to?
>>
>> If you'd done: SELECT (LEAD(col) - LAG(col)) OVER (...)  could
>> PostgreSQL just assume that you meant to link both the lead and lag to
>> the same over clause?
>
>
> Well, if there is only a single aggregate function in the expression there
> isn't any ambiguity.  If there happened to be more than one the system could
> emit a parsing error saying as much.  While likely more user-friendly I
> don't imagine its worth the headache in the parser.

Perhaps, but I guess it would be pretty hard to know what's an
aggregate and what's a window function when there are multiple.

Consider:

SELECT (SUM(x) - SUM(y)) OVER w1 FROM t WINDOW w1 AS (...);

Is SUM(x) an aggregate or a window function? how about SUM(y)? one of
them must be since there's an OVER clause.

OVER is also quite like FILTER, so someone may expect us to also support:

SELECT (SUM(x) - SUM(y)) FILTER(WHERE x > 0) FROM t;

So I think we're pretty good to leave this untouched.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services