clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (40591 in the last week).

select version();
version
PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
 hidden batch(es)


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);
 hidden batch(es)


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; $$
 hidden batch(es)


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


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; $$
 hidden batch(es)


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


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;
8 rows affected
key_name context created_at ordercol
A.1 ctx_A 2021-10-28 21:35:23.900572+01 1
A.2 ctx_A 2021-10-28 21:35:23.900572+01 2
A.3 ctx_A 2021-10-28 21:35:23.900572+01 3
A.4 ctx_A 2021-10-28 21:35:23.900572+01 4
B.1 ctx_B 2021-10-28 21:35:23.900572+01 1
B.2 ctx_B 2021-10-28 21:35:23.900572+01 2
B.3 ctx_B 2021-10-28 21:35:23.900572+01 3
B.4 ctx_B 2021-10-28 21:35:23.900572+01 4
 hidden batch(es)


-- 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;
1 rows affected
key_name context created_at ordercol
A.1 ctx_A 2021-10-28 21:35:23.900572+01 1
A.3 ctx_A 2021-10-28 21:35:23.900572+01 2
A.4 ctx_A 2021-10-28 21:35:23.900572+01 3
A.2 ctx_A 2021-10-28 21:35:23.900572+01 4
 hidden batch(es)


-- 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;
1 rows affected
key_name context created_at ordercol
B.1 ctx_B 2021-10-28 21:35:23.900572+01 1
B.4 ctx_B 2021-10-28 21:35:23.900572+01 2
B.2 ctx_B 2021-10-28 21:35:23.900572+01 3
B.3 ctx_B 2021-10-28 21:35:23.900572+01 4
 hidden batch(es)