Comment prétendre enregistrer des enregistrements comme des numéros de ligne d'un éditeur

Vous n'avez pas vraiment besoin d'une gâchette pour y parvenir. Il suffit de faire la clé principale repérable, et vous pouvez supprimer et renumber dans une seule instruction:

with to_move(old_no, new_no) as (
  values (2,4)
), move_row as (   
  update data
    set order_no = new_no
  from to_move
  where order_no = old_no
)
update data
   set order_no = order_no - 1
from to_move
where order_no > old_no
  and order_no <= new_no 
;

Ma question était basée sur l'hypothèse qu'un déclencheur ne peut pas être désactivé alors qu'une procédure de déclenchement est exécutée.

tandis que cela reste vrai, Cette question de sorte que la question montre que vous pouvez réellement utiliser la fonction pg_trigger_depth () code> pour vérifier la profondeur de déclenchement avant strong> un déclencheur la procédure est appelée. De cette façon, la vérification de la profondeur de déclenchement = 0 fait l'affaire pour empêcher une procédure de déclenchement d'appeler un autre. Par exemple. P>

CREATE TABLE commands (
    step integer primary key NOT NULL DEFAULT 1 CONSTRAINT positive_order CHECK (step >= 0),
    command character varying
);
ALTER TABLE commands OWNER TO kagan;

CREATE OR REPLACE FUNCTION update_commands()
RETURNS TRIGGER AS $$
DECLARE max_step integer;
DECLARE rec RECORD;
BEGIN
    select max(step) into max_step from commands;
    if NEW.step is null then RAISE EXCEPTION 'step must have a value'; end if;
    if NEW.step < 1 then RAISE EXCEPTION 'step (%) must be >= 1', NEW.step; end if;
    if NEW.step > max_step then RAISE EXCEPTION 'step (%) must be <= max(step) (%)', NEW.step, max_step; end if;

    -- Temporarily, move the current record at the old position "out of the way"
    -- Don't forget the other columns
    UPDATE commands set step = 0, command = NEW.command where step = OLD.step;

    if NEW.step > OLD.step then
        FOR rec IN
            select step from commands
            where step > OLD.step and step <= NEW.step
            order by step ASC
        LOOP
            UPDATE commands set step =  step - 1 where step = rec.step;
        END LOOP;
    else
        FOR rec IN
            select step from commands
            where step >= NEW.step and step < OLD.step
            order by step DESC
        LOOP
            UPDATE commands set step =  step + 1 where step = rec.step;
        END LOOP;
    end if;

    -- Put the current row back to the new position
    UPDATE commands set step = NEW.step where step = 0;
    RETURN NULL;    -- DO NOT PROCEED 
END;
$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION insert_commands()
RETURNS TRIGGER AS $$
DECLARE max_step integer;
DECLARE rec RECORD;
BEGIN
    if NEW.step < 1 then RAISE EXCEPTION 'step (%) must be >= 1)', NEW.step; end if;

    select max(step) into max_step from commands;
    if max_step is null then
        NEW.step = 1;
    elsif NEW.step > max_step + 1 then
        RAISE EXCEPTION 'step (%) must be <= max(step) + 1 (%)', NEW.step, max_step + 1;
    else
        FOR rec IN select step from commands where step >= NEW.step order by step DESC LOOP
            UPDATE commands set step =  step + 1 where step = rec.step;
        END LOOP;
    end if;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION delete_commands()
RETURNS TRIGGER AS $$
DECLARE rec RECORD;
BEGIN
        FOR rec IN select step from commands where step > OLD.step order by step ASC LOOP
            UPDATE commands set step =  step - 1 where step = rec.step;
        END LOOP;
    RETURN OLD;
END;
$$ language 'plpgsql';

CREATE TRIGGER insert_commands_trigger BEFORE INSERT ON commands FOR EACH ROW WHEN (pg_trigger_depth() = 0) EXECUTE PROCEDURE insert_commands();
CREATE TRIGGER delete_commands_trigger AFTER DELETE ON commands FOR EACH ROW WHEN (pg_trigger_depth() = 0) EXECUTE PROCEDURE delete_commands();
CREATE TRIGGER update_commands_trigger BEFORE UPDATE ON Commands FOR EACH ROW WHEN (pg_trigger_depth() = 0) EXECUTE PROCEDURE update_commands();

COPY commands (step, command) FROM stdin;
1   CAM - ON
2   Turn left
3   Take picture
\.

Vos données ne sont pas vraiment un bon match pour une base de données relationnelle - si c'était moi, et que le nombre de commandes n'est pas trop grand, je ne le stockerais pas comme un clob JSON ou XML.

mais si vous doit utiliser le stockage relationnel, je pense que devoir mettre à jour la moitié moyenne de toutes les lignes après chaque insertion n'est pas un bon design. P>

Je suggérerais de ne pas stocker le nombre réel, mais il suffit d'avoir la colonne ord code> définir un ordre relatif. Comme @gmb a déjà écrit , vous pouvez ensuite utiliser Row_Number CODE> Pour obtenir vos numéros consécutifs à partir de 1. P>

Pour insérer de nouvelles lignes sans avoir à renuméroter les existants, laissez "trous" Dans la numérotation: p>

Soit min et max être le nombre minimum et maximum que vous souhaitez utiliser dans le ord code> colonne. Ensuite, la toute première ligne que vous insérez doit obtenir p> xxx pré>

pour insérer une nouvelle ligne entre deux lignes existantes avec des numéros de commande o1 code> et o2 code>, utilisez p>

let next = SELECT MIN(ord) FROM commands WHERE ord > :o1
if next IS NULL then
    if o1 == MAX     then panic_or_renumber
    if o1 == MAX - 1 then MAX
    else (o1 + MAX) / 2
else
    if next == o1 + 1 then panic_or_renumber
    else (o1 + next) / 2