这篇文章是基于之前的回答进行适应而来。当应用程序需要变量(即EAV模型中的记录)的标签顺序(也可以是备选键的一部分)时,这种情况经常发生。
priority
字段需要保持连续,这就是所谓的"标签顺序"
- 在插入时: 所有优先级大于等于新记录的记录应该增加他们的优先级
- 类似地,在删除时:减小优先级
- 如果一条记录的优先级被更新,那么在旧的和新的优先级值之间的记录应该将它们的优先级上移或下移。
- 为了避免递归触发器调用:
- 触发器更新翻转任何记录的翻转标志。
- 并测试
old.flipflag=new.flipflag
以检测真正的更新(不是由触发器引起的那些更新)。
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;
CREATE TABLE fruits
( id INTEGER NOT NULL PRIMARY KEY
, priority INTEGER NOT NULL
, flipflag boolean NOT NULL default false
, zname varchar NOT NULL
, CONSTRAINT unique_priority UNIQUE (priority) DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO fruits(id,zname,priority) VALUES
(1 , 'Pear' ,4)
,(2 , 'Apple' ,2)
,(3 , 'Orange' ,1)
,(4 , 'Banana' ,3)
,(5 , 'Peach' ,5)
;
CREATE function shift_priority()
RETURNS TRIGGER AS $body$
BEGIN
UPDATE fruits fr
SET priority = priority +1
, flipflag = NOT flipflag
WHERE NEW.priority < OLD.priority
AND OLD.flipflag = NEW.flipflag
AND fr.priority >= NEW.priority
AND fr.priority < OLD.priority
AND fr.id <> NEW.id
;
UPDATE fruits fr
SET priority = priority -1
, flipflag = NOT flipflag
WHERE NEW.priority > OLD.priority
AND OLD.flipflag = NEW.flipflag
AND fr.priority <= NEW.priority
AND fr.priority > OLD.priority
AND fr.id <> NEW.id
;
RETURN NEW;
END;
$body$
language plpgsql;
CREATE function shift_down_priority()
RETURNS TRIGGER AS $body$
BEGIN
UPDATE fruits fr
SET priority = priority -1
, flipflag = NOT flipflag
WHERE fr.priority > OLD.priority
;
RETURN NEW;
END;
$body$
language plpgsql;
CREATE function shift_up_priority()
RETURNS TRIGGER AS $body$
BEGIN
UPDATE fruits fr
SET priority = priority +1
, flipflag = NOT flipflag
WHERE fr.priority >= NEW.priority
;
RETURN NEW;
END;
$body$
language plpgsql;
CREATE TRIGGER shift_priority_u
AFTER UPDATE OF priority ON fruits
FOR EACH ROW
WHEN (OLD.flipflag = NEW.flipflag AND OLD.priority <> NEW.priority)
EXECUTE PROCEDURE shift_priority()
;
CREATE TRIGGER shift_priority_d
AFTER DELETE ON fruits
FOR EACH ROW
EXECUTE PROCEDURE shift_down_priority()
;
CREATE TRIGGER shift_priority_i
BEFORE INSERT ON fruits
FOR EACH ROW
EXECUTE PROCEDURE shift_up_priority()
;
\echo Pears are Okay
UPDATE fruits
SET priority = 1
WHERE id=1;
SELECT * FROM fruits ORDER BY priority;
\echo dont want bananas
DELETE FROM fruits WHERE id = 4;
SELECT * FROM fruits ORDER BY priority;
\echo We want Kiwis
INSERT INTO fruits(id,zname,priority) VALUES (4 , 'Kiwi' ,3) ;
SELECT * FROM fruits ORDER BY priority;
结果:
Pears are Okay
UPDATE 1
id | priority | flipflag | zname
----+----------+----------+--------
1 | 1 | f | Pear
3 | 2 | t | Orange
2 | 3 | t | Apple
4 | 4 | t | Banana
5 | 5 | f | Peach
(5 rows)
dont want bananas
DELETE 1
id | priority | flipflag | zname
----+----------+----------+--------
1 | 1 | f | Pear
3 | 2 | t | Orange
2 | 3 | t | Apple
5 | 4 | t | Peach
(4 rows)
We want Kiwis
INSERT 0 1
id | priority | flipflag | zname
----+----------+----------+--------
1 | 1 | f | Pear
3 | 2 | t | Orange
4 | 3 | f | Kiwi
2 | 4 | f | Apple
5 | 5 | f | Peach
(5 rows)
flipflag
列旁边放一个眨眼的笑脸是很不错,但我完全不知道它为什么在那里。 - IMSoP