改编自上一个答案.当应用程序需要一个 tabbing-order 变量(读取:EAV 模型中的记录)时，这种事情经常发生，它也可能是(部分)备用键.

Adapted this from a previous answer. This kind of stuff happens often when applications want a tabbing-order for variables (read: records in an EAV model) , which could also be (part of) an alternate key.

• priority 字段需要保持连续.[这是tabbing-order]
• 在插入时:所有具有优先级 >= 新记录的记录的优先级应该增加
• 同样:在 DELETE -> 递减
• 如果一条记录的优先级被 UPDATE 改变，那么旧优先级值和新优先级值之间的记录的优先级应该向上或向下移动.
• 避免递归触发器调用:
  • 基于触发器的更新会翻转它们接触的任何记录的flipflag.
  • 他们测试old.flipflag=new.flipflag 以检测真实 更新.(那些不是由触发器引起的)
  • the priority field needs to be kept consecutive. [this is the tabbing-order]
  • on INSERT: all records with priority >= the new record should have their priorities incremented
  • similarly: on DELETE -> decremented
  • if a record's priority is altered by an UPDATE, the records between the old and the new priority value should have their priorities shifted up or down.
  • to avoid recursive trigger invocation:
    • the trigger-based updates flip the flipflag of any record they touch.
    • And they test for old.flipflag=new.flipflag to detect real updates. (those not caused by a trigger)

            -- Make some data
    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)
            ;
    
            -- Trigger functions for Insert/update/delete
    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 function shift_down_priority()
    RETURNS TRIGGER AS $body$
    
    BEGIN
    
            UPDATE fruits fr
            SET priority = priority -1
            , flipflag = NOT flipflag       -- alternating bit protocol ;-)
            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       -- alternating bit protocol ;-)
            WHERE fr.priority >= NEW.priority
                    ;
            RETURN NEW;
    END;
    
    $body$
    language plpgsql;
    
            -- Triggers for Insert/Update/Delete
    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()
            ;
    
            -- Do some I/U/D operations
    echo Pears are Okay
    UPDATE fruits
    SET priority = 1
    WHERE id=1; -- 1,4
    
    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)