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.
