Re: Using a TRIGGER with window functions.

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