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