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?
I believe you don't need a loop here:
CREATE OR REPLACE FUNCTION degrade_task_priorities()
RETURNS trigger AS $degrade_priority_task_trigger$
DECLARE
r task%rowtype;
BEGIN
UPDATE task SET priority = priority + 1 WHERE task.priority >= NEW.PRIORITY;
RETURN NEW;
END
$degrade_priority_task_trigger$ LANGUAGE plpgsql;