• Empleos
  • Sobre nosotros
  • profesionales
    • Inicio
    • Empleos
    • Cursos y retos
    • Preguntas
    • Profesores
  • empresas
    • Inicio
    • Publicar vacante
    • Nuestro proceso
    • Precios
    • Pruebas Online
    • Nómina
    • Blog
    • Comercial
    • Calculadora de salario

0

417
Vistas
How does trigger update value in for loop, Postgresql?

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?

about 3 years ago · Santiago Trujillo
1 Respuestas
Responde la pregunta

0

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;
about 3 years ago · Santiago Trujillo Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Nuestro proceso Comercial
Legal
Términos y condiciones Política de privacidad
© 2025 PeakU Inc. All Rights Reserved.

Andres GPT

Recomiéndame algunas ofertas
Necesito ayuda