I wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> On Sun, Aug 15, 2021 at 1:24 AM Pól Ua Laoínecháin <linehanp@tcd.ie> wrote:
>>> Why are window functions now allowed in UPDATEs
> I suspect the error check was just copied from the aggregate-function
> case. It's clear why we can't put aggregates in UPDATE: there'd no
> longer be a one-to-one correspondence with original rows. But that
> argument doesn't hold for window functions, so at least in principle
> it seems like we could allow it. The utility doesn't seem very high
> though, so if it takes more work than "delete the error check" I'm
> not sure anyone will care to bother.
Out of curiosity, I did spend a few minutes poking at this, and as
I feared it's not all that trivial. I think that the planner and
executor might mostly Just Work, but there are various gaps in the
parser. One interesting point is that the UPDATE syntax has no
provision for a WINDOW clause, so there'd be no way to share window
definitions across different window functions. While that's not exactly
a deal-breaker, it'd be weird compared to how things work in SELECT.
Would we be willing to go to the trouble of adding such a clause to the
syntax? I dunno; it'd certainly enlarge the footprint of a patch for
this by a lot.
regards, tom lane