如何使postgres函数对表示链表的行进行重新排序

Question

我有一个这样的表，它表示一个链表。当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)有两个参数，id_to_move是要移动到新位置的id，after_id是要移动该行的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"

Answer 1

如果要指定订单，则必须使用order BY子句。任何其他解决方案都不应该奏效。你的设计对更大的数据不实用。在您的设计中，每次都必须为order计算一些值，并且这种计算应该基于递归调用——这对图数据库来说是好的设计，对关系数据库来说是坏的设计。

关系(表(不是矩阵，没有开始，也没有结束。例如，当你想搜索最后一条记录时，你必须使用递归CTE

-- searching last record in list
with recursive x as (select 0 l, id 
from mytable 
where comes_after is null 
union all 
select l + 1, mytable.id 
from x join mytable on x.id = mytable.comes_after) 
select id 
from x 
order by l desc 
limit 1;

我不知道你的目标是什么，但关系数据库是不好的工具。

这可能是一项有趣的学校任务，但在现实生活中可能很糟糕。这违背了关系数据库的原则。

更常见的解决方案是使用可以用于ORDERBY子句的特殊数字列。有些像

CREATE SEQUENCE test_o START WITH 1;
CREATE TABLE test(id SERIAL, v varchar, o numeric DEFAULT nextval('test_o'));
-- insert at end
INSERT INTO test(v) VALUES('ahoj');
INSERT INTO test(v) VALUES('nazdar');
INSERT INTO test(v) VALUES('bazar');
-- sort data by o
SELECT * FROM test ORDER BY o;
INSERT INTO test(v, 
SELECT * FROM test ORDER BY o;
┌────┬────────┬───┐
│ id │   v    │ o │
╞════╪════════╪═══╡
│  1 │ ahoj   │ 1 │
│  2 │ nazdar │ 2 │
│  3 │ bazar  │ 3 │
└────┴────────┴───┘

在id=2:之后插入

INSERT INTO test(v, o)
SELECT 'HELLO', 
(SELECT (o +  lead(o,1) OVER (ORDER BY o))/2 
FROM test 
WHERE o >= (SELECT o 
FROM test 
WHERE id = 2) 
ORDER BY o 
LIMIT 1);
postgres=# SELECT * FROM test ORDER BY o;
┌────┬──────────┬────────────────────┐
│ id │    v     │         o          │
╞════╪══════════╪════════════════════╡
│  1 │ ahoj     │                  1 │
│  2 │ nazdar   │                  2 │
│  6 │ HELLO    │ 2.5000000000000000 │
│  3 │ bazar    │                  3 │
└────┴──────────┴────────────────────┘
(4 rows)