User-Defined Ordering in PostgreSQL using Stored Array

stored-array.sql

-- from the People, Postgres, Data Discord https://discord.com/channels/710918545906597938/710918545906597941/1214677867431206932

CREATE TABLE todo_list (
  id          INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  list_name   TEXT NOT NULL,
  item_order  INT[] NULL
);

CREATE TABLE todo_list_item (
  id          INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  list_id     INT NOT NULL REFERENCES TODO_LIST,
  item_name   TEXT NOT NULL
);

CREATE OR REPLACE FUNCTION f_add_list_item()
RETURNS TRIGGER AS
$$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE todo_list
       SET item_order = array_append(item_order, NEW.id)
     WHERE id = NEW.list_id;
  ELSEIF TG_OP = 'DELETE' THEN
    UPDATE todo_list
       SET item_order = array_append(item_order, OLD.id)
     WHERE id = OLD.list_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_update_list_item_order
 AFTER INSERT OR UPDATE OR DELETE
    ON todo_list_item
   FOR EACH ROW EXECUTE FUNCTION f_add_list_item();

INSERT INTO todo_list (list_name) VALUES ('My List');

INSERT INTO todo_list_item (list_id, item_name)
SELECT 1, 'Gotta do ' || a.id
  FROM generate_series(1, 5) a(id);

SELECT l.list_name, i.item_name
  FROM (select id, list_name, unnest(item_order) AS item_id
          FROM todo_list) l
  JOIN todo_list_item i on (i.list_id = l.id AND i.id = l.item_id)
 WHERE l.list_name = 'My List';

 list_name | item_name  
-----------+------------
 My List   | Gotta do 1
 My List   | Gotta do 2
 My List   | Gotta do 3
 My List   | Gotta do 4
 My List   | Gotta do 5

UPDATE todo_list
   SET item_order = ARRAY[3, 4, 5, 2, 1]
 WHERE list_name = 'My List';

SELECT l.list_name, i.item_name
  FROM (select id, list_name, unnest(item_order) AS item_id
          FROM todo_list) l
  JOIN todo_list_item i on (i.list_id = l.id AND i.id = l.item_id)
 WHERE l.list_name = 'My List';

 list_name | item_name  
-----------+------------
 My List   | Gotta do 3
 My List   | Gotta do 4
 My List   | Gotta do 5
 My List   | Gotta do 2
 My List   | Gotta do 1