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...