Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


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)
);
INSERT INTO t1 VALUES
(101, 123),
(101, 124),
(101, 125),
(102, 123),
(102, 124);
5 rows affected

--
-- DESIRED RESULT
--
-- 101, 123, 0
-- 101, 124, 1
-- 101, 125, 2
-- 102, 123, 0
-- 102, 124, 1
--
UPDATE t1
SET sort_order = activity_id - FIRST_VALUE(activity_id) 
                   OVER (PARTITION BY user_id 
                   ORDER BY     user_id, activity_id);
ERROR:  window functions are not allowed in UPDATE
LINE 2: SET sort_order = activity_id - FIRST_VALUE(activity_id) 
                                       ^


CREATE TABLE t2
(
  user_id     SMALLINT NOT NULL,
  activity_id SMALLINT NOT NULL,
  sort_order  SMALLINT 
    GENERATED ALWAYS AS 
    (
      activity_id - FIRST_VALUE(activity_id) 
                    OVER (PARTITION BY user_id 
                          ORDER BY     user_id, activity_id)
    ) STORED,
  CONSTRAINT t2_uid_aid_uq UNIQUE (user_id, activity_id)
);
ERROR:  window functions are not allowed in column generation expressions
LINE 8:       activity_id - FIRST_VALUE(activity_id) 
                            ^


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;
5 rows affected

SELECT * FROM t1;


user_id
activity_id
sort_order


101
123
0

101
124
1

101
125
2

102
123
0

102
124
1


-- 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;
$$
ERROR:  syntax error at or near "RETURNS"
LINE 2:   RETURNS TRIGGER 
          ^


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
  )
  UPDATE t1
  SET sort_order = cte.so
  FROM cte
  WHERE NEW.user_id = cte.user_id
    AND NEW.activity_id = cte.activity_id;

  RETURN NEW;
END;
$$
CREATE TRIGGER sort_order_tr
  BEFORE INSERT
  ON t1
  FOR EACH ROW
  EXECUTE PROCEDURE update_t1_sort_order();
INSERT INTO t1 VALUES (102, 125);
1 rows affected

SELECT * FROM t1;


user_id
activity_id
sort_order


101
123
0

101
124
0

101
125
0

102
123
0

102
124
0

102
125
null