Re: Proposal: QUALIFY clause

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.

--

Vik Fearing