Tengo una tabla que contiene tareas. Cada tarea tiene una prioridad. Cuando inserto una nueva tarea, si la nueva tarea tiene la misma prioridad que las antiguas, las antiguas que tienen las prioridades más bajas deben ser degradadas.

Por ejemplo, tenemos 3 tareas con prioridades: 1, 2, 3. Si inserto una nueva tarea con prioridad 1, todas las tareas antiguas deben cambiar la prioridad a 2, 3, 4 antes de que se inserte la nueva tarea.

Para hacer eso, uso 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();

El problema es cuando trato de insertar 4 tareas nuevas con la misma prioridad 1, el resultado final son 4 registros con prioridad como estos: 1, 4, 6, 7. Debería haber sido 1,2,3,4.

Dudo que no entienda la forma en que el bucle y el disparador funcionan juntos. ¿Dónde lo hice mal?