Re: Proposal: QUALIFY clause

On Tue Jul 22, 2025 at 3:11 PM -03, Marcos Pegoraro wrote:
> Em ter., 22 de jul. de 2025 às 08:56, Matheus Alcantara <
> matheusssilv97(at)gmail(dot)com> escreveu:
>
>> The current patch supports the following syntaxes:
>> SELECT a, b, c
>> wf() OVER () as d
>> FROM tab
>> QUALIFY d = 1
>> When using the "QUALIFY d = 1" form, I currently rewrite the expression
>> as "wf() OVER () = 1" by searching the targetlist for the matching
>> alias, replacing the Var with the corresponding WindowFunc.
>>
>
> Not related to $subject but that way you did,
> that replacement alias for wf expression, is cool.
> With that would it be possible to have where replacements too ? Maybe.
>
> select a+b as ab from t where ab = 5
>
Do you mean instead of reference the "ab" using a Var it replaces with a
OpExpr of "a+b"? I think that it would be possible, but the current
implementation is not fully correct, it only works for OpExpr's. Using
AND/OR operators does not work and I think that to make it fully correct
is more complex, so I think that rewriting the query to use a subquery
as Vik suggested on [1] fixes this issue and also makes it easier to
understand.

(this happens on transformQualifyClause() if you want to take a look)

[1] https://www.postgresql.org/message-id/6c998e4f-f6f2-43c2-8b67-cfff360ef241%40postgresfriends.org

--
Matheus Alcantara