У меня есть такая таблица, которая представляет собой связанный список. Когда comes_after столбец равен null, это означает, что это первая запись в связанном списке.

id      | comes_after
--------+------------
"one"   | null
"two"   | "one"
"three" | "two"
"four"  | "three"

Как мне написать функцию с использованием SQL или PLPGSQL для изменения порядка строк? Функция function move_id_after (id_to_move string, after_id string) имеет 2 аргумента: id_to_move - идентификатор для перехода в новую позицию, а after_id - идентификатор, после которого следует переместить эту строку. Если after_id равно нулю, значит переместить его в начало списка.

Это моя попытка, но она не работает и не кажется идеальным способом сделать это. Как показано в примерах случаев, я хотел бы также иметь возможность перемещать строку в самое начало списка или в самый конец и обрабатывать случаи, когда ничего не нужно менять.

create function move_id_after (id_to_move string, after_id string) language plpgsql as $$
declare
  AFTER_id_to_move string;
  AFTER_after_id string;
  id_to_move_used_to_follow string;
begin
  select id from mytable where comes_after = id_to_move into AFTER_id_to_move;
  select id from mytable where comes_after = after_id into AFTER_after_id;
  update mytable set comes_after = id_to_move where id = AFTER_after_id;
  update mytable set comes_after = AFTER_after_id where id = id_to_move returning id into id_to_move_used_to_follow;
  update mytable set comes_after = id_to_move_used_to_follow where id = id_to_move_after;
end $$;

Вот примеры некоторых случаев, каким должен быть результат.

Переместить запись в другую позицию

select move_id_after("two", "three") должен стать:

id      | comes_after
--------+------------
"one"   | null
"three" | "one"
"two"   | "three"
"four"  | "two"

Переместить запись в позицию, в которой она уже находится

select move_id_after("three", "two") не должно быть изменений:

id      | comes_after
--------+------------
"one"   | null
"two"   | "one"
"three" | "two"
"four"  | "three"

Переместить первую запись на последнюю позицию

select move_id_after("one", "four") должен стать:

id      | comes_after
--------+------------
"two"   | null
"three" | "two"
"four"  | "three"
"one"   | "four"

Переместить последнюю запись в первую позицию

select move_id_after("four", null) должен стать:

id      | comes_after
--------+------------
"four"  | null
"one"   | "four"
"two"   | "one"
"three" | "two"