select version();

CREATE TABLE keys ( key_name VARCHAR(128) UNIQUE NOT NULL PRIMARY KEY, context VARCHAR(128) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), orderCol INTEGER NOT NULL DEFAULT (0) ); CREATE INDEX Ix_Position ON keys (context, orderCol);

CREATE FUNCTION insertNew() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ BEGIN NEW.orderCol := COALESCE((SELECT MAX(orderCol) FROM keys WHERE context = NEW.context), 0) + 1; return NEW; END; $$

CREATE TRIGGER checkOrderInsert BEFORE INSERT ON keys FOR EACH ROW EXECUTE PROCEDURE insertNew();

CREATE FUNCTION updateExisting() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ BEGIN IF NEW.orderCol < OLD.orderCol THEN UPDATE keys SET orderCol = orderCol + 1 WHERE context = NEW.context AND orderCol >= NEW.orderCol AND orderCol < OLD.orderCol; ELSEIF NEW.orderCol > OLD.orderCol THEN UPDATE keys SET orderCol = orderCol - 1 WHERE context = NEW.context AND orderCol > OLD.orderCol; END IF; return NEW; END; $$

CREATE TRIGGER checkOrderUpdate BEFORE UPDATE ON keys FOR EACH ROW WHEN (pg_trigger_depth() < 1) EXECUTE PROCEDURE updateExisting();

INSERT INTO keys (key_name, context) VALUES ('A.1', 'ctx_A'), ('A.2', 'ctx_A'), ('A.3', 'ctx_A'), ('A.4', 'ctx_A'), ('B.1', 'ctx_B'), ('B.2', 'ctx_B'), ('B.3', 'ctx_B'), ('B.4', 'ctx_B'); SELECT * FROM keys ORDER BY context, orderCol;

-- new > old 2 => 4 UPDATE keys SET orderCol = 4 WHERE context = 'ctx_A' AND orderCol = 2; SELECT * FROM keys WHERE context = 'ctx_A' ORDER BY context, orderCol;

-- new < old 4 => 2 UPDATE keys SET orderCol = 2 WHERE context = 'ctx_B' AND orderCol = 4; SELECT * FROM keys WHERE context = 'ctx_B' ORDER BY context, orderCol;