sql - 在插入/更新之前使用触发器重新排序相邻行(更高/更低)

Question

给定下表books

 id | listorder 
----+-----------
  3 |         1
  2 |         2
  1 |         3
  4 |         4
  6 |         5
  7 |         6
  5 |         7

我可以通过执行以下命令将 id=3 的行更新为 listorder=6，在更新新位置之前首先对相邻行重新排序:

UPDATE books
SET listorder = listorder - 1
WHERE listorder <= 6 -- The new position
  AND listorder > (SELECT listorder 
                   FROM books WHERE id = 3);

UPDATE books
SET listorder = 6 -- The new position
WHERE id = 3;

我如何创建一个触发器函数，它将在之前 UPDATE 或 INSERT 运行，这样我需要做的就是更新列表顺序，其他行将预先自动重新排序，无论在列表顺序中是上升还是下降？

Answer 1

你必须防止递归触发。使用 pg_trigger_depth()函数以确保每个用户的 insert 或 update 仅触发一次该函数。

表格

create table books (id int, listorder int);
insert into books values
(3, 1),
(2, 2),
(1, 3),
(4, 4),
(6, 5),
(7, 6),
(5, 7);

触发器

create or replace function books_trigger()
returns trigger language plpgsql as $$
begin
    if tg_op = 'UPDATE' then
        if new.listorder > old.listorder then
            update books
            set listorder = listorder- 1
            where listorder <= new.listorder
            and listorder > old.listorder
            and id <> new.id;
        else
            update books
            set listorder = listorder+ 1
            where listorder >= new.listorder
            and listorder < old.listorder
            and id <> new.id;
        end if;
    else
        update books
        set listorder = listorder+ 1
        where listorder >= new.listorder
        and id <> new.id;
    end if;
    return new;
end $$;

create trigger books_trigger
before insert or update on books
for each row when (pg_trigger_depth() = 0)
execute procedure books_trigger();

测试

插入

insert into books values (8, 5);
table books order by 2;

 id | listorder
----+-----------
  3 |         1
  2 |         2
  1 |         3
  4 |         4
  8 |         5
  6 |         6
  7 |         7
  5 |         8
(8 rows)

更新

update books set listorder = 2 where id = 8;
table books order by 2;

 id | listorder
----+-----------
  3 |         1
  8 |         2
  2 |         3
  1 |         4
  4 |         5
  6 |         6
  7 |         7
  5 |         8
(8 rows)

update books set listorder = 4 where id = 8;
table books order by 2;

 id | listorder
----+-----------
  3 |         1
  2 |         2
  1 |         3
  8 |         4
  4 |         5
  6 |         6
  7 |         7
  5 |         8
(8 rows)

触发器在具有相同 listorder 的多行插入上运行良好:

insert into books values (9, 4), (10, 4), (11, 4), (12, 4);
table books order by 2;

 id | listorder
----+-----------
  3 |         1
  2 |         2
  1 |         3
 12 |         4
 11 |         5
 10 |         6
  9 |         7
  8 |         8
  4 |         9
  6 |        10
  7 |        11
  5 |        12
(12 rows)

但它不允许多行更新:

update books set listorder = 4
where id > 8;

ERROR:  tuple to be updated was already modified by an operation triggered by the current command
HINT:  Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows.

在保留列 listorder 的唯一性的上下文中，这是合乎逻辑且合乎需要的行为，因为此查询的结果是不明确的。