如何实现SQL中的优先级（postgres） [英] How to implement priorities in SQL (postgres)
问题描述
我正在写一些需要在数据库中存储项目的软件，这些项目需要有一个优先级，所以我们最终得到
ID |名称|优先级
-------- + -------------- + ----------
1 | Pear | 4
2 |苹果| 2
3 |橙色| 1
4 |香蕉| 3
现在，最优先的水果是橘子，苹果，香蕉， p>
现在，我想让梨成为第一优先，所以梨，橙，苹果，香蕉。表格将如下所示：
ID |名称|优先级
-------- + -------------- + ----------
1 | Pear | 1
2 |苹果| 3
3 |橙色| 2
4 |香蕉| 4
使用PHP和Postgres实现这个最好的方法是什么。考虑到表不会超过约12-13个项目，我已经考虑过选择整个表，并重写优先级之前UPDATING一切回来。
*重要*
优先级可以按任何顺序更改，因此优先级7可以设置为优先级3缺口），我们需要关闭优先级为7的项目，优先级为3的项目已经保留在优先级列表中。
好的，这里是我试图保持优先级独特和连续。由trigger +函数实现。困难的部分是避免可能来自触发器内的更新的无限递归。这是由一个污垢/颜色标志，它必须放置在表内解决。它的价值不重要;只有改变它。
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp;
SET search_path = tmp;
CREATE TABLE fruits
（id INTEGER NOT NULL PRIMARY KEY
，zname varchar NOT NULL
，priority INTEGER NOT NULL
，flipflag boolean NOT NULL default false
，CONSTRAINT unique_priority UNIQUE（priority）DEFERRABLE INITIALLY DEFERRED
）;
INSERT INTO fruits（id，zname，priority）VALUES
（1，'Pear'，4）
，（2，'Apple'，2）
， 'Orange'，1）
，（4，'Banana'，3）
;
CREATE函数shift_priority（）
返回TRIGGER作为$ 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 TRIGGER shift_priority
更新优先级水果
对于每个行
WHEN（OLD.flipflag = NEW.flipflag AND OLD.priority<> .priority）
EXECUTE PROCEDURE shift_priority（）
;
UPDATE fruits
SET priority = 1
WHERE id = 1;
结果：
code> SELECT * FROM fruits ORDER BY id;
注意：drop cascades到另外两个对象
详细信息：drop cascades to table tmp.fruits
drop cascades to function tmp.shift_priority（）
DROP SCHEMA
CREATE SCHEMA
SET
注意：CREATE TABLE / PRIMARY KEY将为表fruits创建隐含索引fruits_pkey
注意：CREATE TABLE / UNIQUE将为表fruits创建隐式索引unique_priority
CREATE TABLE
INSERT 0 4
CREATE FUNCTION
CREATE TRIGGER
UPDATE 1
id | zname |优先级| flipflag
---- + -------- + ---------- + ----------
1 | Pear | 1 | f
2 |苹果| 3 | t
3 |橙色| 2 | t
4 |香蕉| 4 | t
（4 rows）
I'm writing some software that requires storing items in a database, the items need to have a 'priority' so we end up with
ID | Name | Priority
--------+--------------+----------
1 | Pear | 4
2 | Apple | 2
3 | Orange | 1
4 | Banana | 3
So now, the top priority fruit is the Orange, then Apple then Banana then Pear.
Now, I want to make Pear the number one priority so Pear, Orange, Apple, Banana. The table will look like:
ID | Name | Priority
--------+--------------+----------
1 | Pear | 1
2 | Apple | 3
3 | Orange | 2
4 | Banana | 4
Whats the best way to achieve this with PHP and Postgres. Given the table is not going to be more than about 12-13 items I've thought about SELECTing the entire table and rewriting the Priorities before UPDATING everything back.
* Important *
The priorities can be changed in any order, so priority 7 could be set to priority 3 (thus moving everything below priority 3 down a notch), and we need to close the gap the item with priority 7 which was moved to priority 3 has left in the priority list.
Ok, here is my attempt to keep the priorities unique and consecutive. Implemented by a trigger+function. The hard part is to avoid infinite recursion that could resulting from the updates from within the trigger. That is solved by a dirt/color flag, which has to be placed inside the table. Its value is not important; only the change of it.
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;
CREATE TABLE fruits
( id INTEGER NOT NULL PRIMARY KEY
, zname varchar NOT NULL
, priority INTEGER NOT NULL
, flipflag boolean NOT NULL default false
, 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)
;
CREATE function shift_priority()
RETURNS TRIGGER AS $body$
BEGIN
UPDATE fruits fr
SET priority = priority +1
, flipflag = NOT flipflag -- alternating bit protocol ;-)
WHERE NEW.priority < OLD.priority
AND OLD.flipflag = NEW.flipflag -- redundant condition
AND fr.priority >= NEW.priority
AND fr.priority < OLD.priority
AND fr.id <> NEW.id -- exlude the initiating row
;
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 TRIGGER shift_priority
AFTER UPDATE OF priority ON fruits
FOR EACH ROW
WHEN (OLD.flipflag = NEW.flipflag AND OLD.priority <> NEW.priority)
EXECUTE PROCEDURE shift_priority()
;
UPDATE fruits
SET priority = 1
WHERE id=1;
RESULTS:
SELECT * FROM fruits ORDER BY id;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table tmp.fruits
drop cascades to function tmp.shift_priority()
DROP SCHEMA
CREATE SCHEMA
SET
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fruits_pkey" for table "fruits"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "unique_priority" for table "fruits"
CREATE TABLE
INSERT 0 4
CREATE FUNCTION
CREATE TRIGGER
UPDATE 1
id | zname | priority | flipflag
----+--------+----------+----------
1 | Pear | 1 | f
2 | Apple | 3 | t
3 | Orange | 2 | t
4 | Banana | 4 | t
(4 rows)
这篇关于如何实现SQL中的优先级（postgres）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
