David Fetter <david@fetter.org> writes:
> Basically, there is no way I've found so far to qualify any window
> function in the target list, which makes a giant POLA violation.
The FM points out in at least two places that window functions logically
execute on the output of the WHERE/GROUP BY/HAVING steps. It's
conceptually nonsensical to have window function calls in those clauses,
just like it's conceptually nonsensical to use aggregates in WHERE.
Therefore, if you need to filter on the results of the window functions,
you put them into a sub-select and write the filter condition in the
outer query. This is required by spec, and it really does not matter
whether you find it astonishing.
regards, tom lane