Re: Using a TRIGGER with window functions.

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Sun, Aug 15, 2021 at 1:24 AM Pól Ua Laoínecháin <linehanp(at)tcd(dot)ie> wrote:
>> Why are window functions now allowed in UPDATEs

> You can get it to work via a subquery/FROM clause computation. That it
> doesn't work directly in the SET clause I don't know off-hand, but most
> likely the development and runtime cost of making it work isn't worth the
> benefit.

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.

>> Why aren't window functions allowed in GENERATED columns?

> Because the expressions allowed in GENERATED can only immutably reference
> other columns in the same row. The underlying rationale is probably quite
> similar to the UPDATE comment above.

Yeah --- GENERATED is supposed to be immutable, and there's about 0%
chance that anything involving a subquery or window function would
really be immutable. I think there are implementation issues too,
but they're not worth getting into given that point.

regards, tom lane