Hi all,
> If you want to update many rows after updating N rows,
Thanks to those who helped me with this - I think that Michael Lewis
has given me some good ideas on how I should go about this. Also,
thanks to those who took the time to explain why some of my ideas were
not well grounded (wouldn
I wrote:
> "David G. Johnston" writes:
>> On Sun, Aug 15, 2021 at 1:24 AM Pól Ua Laoínecháin 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
>
"David G. Johnston" writes:
> On Sun, Aug 15, 2021 at 1:24 AM Pól Ua Laoínecháin 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
On Sun, Aug 15, 2021 at 1:24 AM Pól Ua Laoínecháin wrote:
>
> ERROR: window functions are not allowed in UPDATE LINE 2: SET
> sort_order = activity_id - FIRST_VALUE(activity_id)
>
> Why are window functions now allowed in UPDATEs
>
You can get it to work via a subquery/FROM clause computation.
If you want to update many rows after updating N rows, you want an after
STATEMENT trigger which is executed after insert, update or delete. You
also want to ensure that only that function maintains sort_order field and
that you don't update recursively, perhaps by executing that function when
NEW.
Hi all,
I have a rather contrived example, but I just can't get this to work.
A fiddle with all my code is available here:
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=9a89ea79ffc617a11c36d63123d2f987
CREATE TABLE t1
(
user_id SMALLINT NOT NULL,
activity_id SMALLINT NOT NULL,
sort_or
