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

Thank you David and David!!

I realized it's simpler to create separate variables (ex, Sum(x) and
SUM(Y)) and put them in an inner table. It's one of those things you look
at with new fresh eyes the next days and it just makes sense.

Appreciate the help!

On Wed, Apr 4, 2018 at 11:02 PM, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> 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
>