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

On 5 April 2018 at 14:40, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Wed, Apr 4, 2018 at 7:32 PM, David Rowley <david(dot)rowley(at)2ndquadrant(dot)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