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;