I have table which contains tasks. Each task has a priority. When I insert a new task, if the new task has the same priority as an old ones, the old ones which have the lower priorities must be down grade.

For example, we have 3 tasks with priorities: 1, 2, 3. If I insert a new task with priority 1, all old tasks must change priority to 2, 3, 4 before the new task is inserted.

In order to do that, I use trigger:

CREATE SEQUENCE task_id_seq;

CREATE TABLE task 
(
    task_id INTEGER NOT NULL DEFAULT nextval('task_id_seq'),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deadline TIMESTAMP WITH TIME ZONE,
    priority INT2 NOT NULL,
    PRIMARY KEY (task_id),
    CONSTRAINT task__check_task_name CHECK(CHAR_LENGTH(name) >= 4),
    CONSTRAINT task__check_unique_task_name UNIQUE(name),
    CONSTRAINT task__check_unique_task_priority UNIQUE(priority)
);

CREATE INDEX priority_index ON task(priority);

CREATE OR REPLACE FUNCTION degrade_task_priorities()
RETURNS trigger AS $degrade_priority_task_trigger$
DECLARE
    r task%rowtype;
BEGIN
    FOR r in SELECT * FROM task WHERE task.priority >= NEW.priority
    LOOP
        --r.priority := r.priority + 1;
        UPDATE task SET priority = priority + 1;
        --WHERE task.priority In (
        --  SELECT priority FROM task WHERE task.priority >= NEW.priority ORDER BY task.priority);
        --RETURN NEXT r;
    END LOOP;
    RETURN NEW;
END
$degrade_priority_task_trigger$ LANGUAGE plpgsql;


CREATE TRIGGER insert_new_task_trigger
BEFORE INSERT ON task
FOR EACH ROW
EXECUTE PROCEDURE degrade_task_priorities();

The problem is when I try to insert 4 new task with the same priority 1, the final result is 4 records with priority like these: 1, 4, 6, 7. I should have been 1,2,3,4.

I doubt that I don't understand the way for loop and trigger work together. Where did I make it wrong?