Using a TRIGGER with window functions.

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_order SMALLINT NULL,

CONSTRAINT t1_uid_aid_uq UNIQUE (user_id, activity_id)
);

Populate:

INSERT INTO t1 VALUES
(101, 123),
(101, 124),
(101, 125),
(102, 123),
(102, 124);

Now, I want to update the sort_order column as follows:

--
-- DESIRED RESULT
--
-- 101, 123, 0
-- 101, 124, 1
-- 101, 125, 2
-- 102, 123, 0
-- 102, 124, 1
--

So, as you can see, I want to update the sort_order column, starting from 0

So, I tried this,

UPDATE t1
SET sort_order = activity_id - FIRST_VALUE(activity_id)
OVER (PARTITION BY user_id
ORDER BY user_id, activity_id);

and this gives

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 - would be very handy to have?

I also tried putting it into a computed/generated column, but got this
error (see fiddle)

ERROR: window functions are not allowed in column generation
expressions LINE 8: activity_id - FIRST_VALUE(activity_id)

Why aren't window functions allowed in GENERATED columns? Seems that
it would be pretty useful functionality to me and would be a trivial
solution for this issue?

Got it to work using a CTE as follows.

WITH cte AS
(
SELECT
user_id,
activity_id,
activity_id - FIRST_VALUE(activity_id)
OVER (PARTITION BY user_id
ORDER BY user_id, activity_id) AS so
FROM t1
)
UPDATE t1
SET sort_order = cte.so
FROM cte
WHERE t1.user_id = cte.user_id
AND t1.activity_id = cte.activity_id;

So, that's all well and good! But what happens when I want to add
records and keep the sort_order in sync with my user_id and
activity_id?

So, I'm trying triggers.

I run

INSERT INTO t1 VALUES (102, 125);

and am desperately trying to get a trigger to suitably update my
sort_order field.

I've tried various solutions - using INSERT/UPDATE and BEFORE/AFTER
INSERT - can't seem to get anything to work. I think that my
underlying understanding of triggers is flawed, so I wonder if anybody
could be so kind as to explain to me where I'm going wrong.

Sample (failing) trigger (see fiddle).

CREATE OR REPLACE FUNCTION update_t1_sort_order()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN
WITH cte AS
(
SELECT
NEW.user_id,
NEW.activity_id,
NEW.activity_id - FIRST_VALUE(NEW.activity_id)
OVER (PARTITION BY NEW.user_id
ORDER BY NEW.user_id, NEW.activity_id) AS so
)

INSERT INTO t1 (user_id, activity_id, sort_order)
VALUES (NEW.user_id, NEW.activity_id, (SELECT so FROM cte));

RETURN NEW;
END;
$$ -- see fiddle...

CREATE TRIGGER sort_order_tr
BEFORE INSERT -- tried AFTER INSERT and various combinations of
UPDATE also... nada!
ON t1
FOR EACH ROW
EXECUTE PROCEDURE update_t1_sort_order();

Either I go into an infinite loop or I make all my sort_orders 0 or
NULL or (best result) ther others untouched and my insert sort_order =
0, I leave the others alone but the newly inserted value has a sort
order of 0. As I said, the example is contrived - basically, I'd like
to learn more about triggers and the logic that makes them "tick".

If triggers aren't the ideal solution, I'm open to other avenues, but
would like to understand where I'm going wrong!

TIA and rgs,

Pól...