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

On 5 April 2018 at 12:23, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Wed, Apr 4, 2018 at 4:41 PM, PG Bug reporting form
>> SELECT *,
>> (P2.received_at - LAG(P2.received_at)) OVER (PARTITION BY anonymous_id
>> ORDER
>> BY P2.received_at DESC) AS time_diff
>> --((EXTRACT(EPOCH FROM (P2.received_at - LEAD(P2.received_at)))/60)) OVER
>> (PARTITION BY P2.anonymous_id ORDER BY P2.received_at DESC) AS
>> time_diff_minutes
>> FROM javascript.pages P2```
>>
>> For the second line, I have to remove the parentheses around
>> (P2.received_at
>> - LAG(P2.received_at)) for it to run. Which doesn't make sense. And more
>> importantly, I can't seem to get the 3rd line (currently commented out) to
>> run because of this issue of Postgres seeming to not allow parentheses
>> before the OVER
>
>
> 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?

If you want to shrink the syntax down a bit, then you can define your
WINDOW clauses at the end of the query:

select lead(...) over w,lag(...) over w from table window w as
(partition by ... order by ...);

This might make it easier to read if you're embedding the window
functions in other expressions.

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