Re: Proposal: QUALIFY clause
Vik Fearing <vik@postgresfriends.org>
From: Vik Fearing <vik@postgresfriends.org>
To: Matheus Alcantara <matheusssilv97@gmail.com>,
Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Peter Eisentraut <peter@eisentraut.org>
Date: 2025-07-25T14:50:12Z
Lists: pgsql-hackers
On 25/07/2025 14:55, Matheus Alcantara wrote: > On Mon Jul 21, 2025 at 7:11 PM -03, Vik Fearing wrote: >> 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. > With this transformation users will see a Subquery plan node even if > it's not present on the original query, is that expected or it can be > confusing to users? This is a definition technique, it does not need to be implemented as a subquery. -- Vik Fearing