Re: Proposal: QUALIFY clause

Hi

út 22. 7. 2025 v 0:12 odesílatel Vik Fearing <vik(at)postgresfriends(dot)org>
napsal:

>
> On 21/07/2025 23:29, Matheus Alcantara wrote:
> > On Mon Jul 21, 2025 at 5:23 PM -03, Vik Fearing wrote:
> >> On 21/07/2025 14:47, Matheus Alcantara wrote:
> >>> Hi all,
> >>>
> >>> I'm sending a proof-of-concept patch to add support for the QUALIFY
> >>> clause in Postgres. This feature allows filtering rows after window
> >>> functions are computed, using a syntax similar to the WHERE or HAVING
> >>> clauses.
> >>
> >> I took a very brief look at this, and I think your grammar is wrong.
> >> The QUALIFY clause should go after the WINDOW clause, just like
> >> FROM/WHERE and GROUP BY/HAVING.
> >>
> >>
> >> That is what I am proposing to the standards committee, and I already
> >> have some buy-in for that.
> >>
> > Thank you for the brief review and for the comments!
> >
> > I'm not sure if I fully understand but please see the new attached
> > version.
>
>
> That is my preferred grammar, thank you. I have not looked at the C
> code by this can be obtained with a syntax transformation. To wit:
>
>
> SELECT a, b, c
> FROM tab
> QUALIFY wf() OVER () = ?
>
>
> can be rewritten as:
>
>
> SELECT a, b, c
> FROM (
> SELECT a, b, c, wf() OVER () = ? AS qc
> FROM tab
> ) AS q
> WHERE qc
>
>
> and then let the optimizer take over. The standard does this kind of
> thing all over the place; I don't know what the postgres project's
> position on doing things like this are.
>

just for curiosity - why the HAVING clause was not used?

Any window functions are +/- an "aggregate" function, and then HAVING looks
more natural to me.

Regards

Pavel

> --
>
> Vik Fearing
>
>
>
>