How to implement priorities in SQL (postgres)

Question

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.

Unmeriting

Answer 1

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 result 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)

Miter

Answer 2

Two updates in a single transaction should work fine on a such small table.

create temp table priorities (
  id integer primary key,
  name varchar(15) not null,
  priority integer not null check (priority > 0 and priority < 100)
);

insert into priorities values 
(1,'Pear',4),
(2,'Apple',2),
(3,'Orange',1),
(4,'Banana',3);

-- Make Pear priority 1.
begin;
update priorities 
set priority = priority + 1
-- The value below is the priority you're aiming for. You want
-- Pear to be #1, so you use ">= 1".
where priority >= 1;

update priorities
set priority = 1 where name = 'Pear';
commit;

For convenience, you can wrap this in a stored procedure.

Padang

+1 - Though Postgres only supports functions, not stored procedures. – Cedillo

What about if I moved item with priority 3 to priority 2 - wouldn't that leave a gap in the priorities? – Unmeriting

I just clarified the last part in the question. – Unmeriting

No, it won't leave a gap if you use the right priority and name. – Cantus