Ordering with PostgreSQL trigger when updating and inserting

I’m looking to sort the rows based on a variable. I’m pulling my hair out trying to wrap my head around this kind of trigger. I’ve only ever written triggers that gets some values from other tables.

Example: I have rows with the columns lets say: name and order.

CREATE TABLE buildorder (
    name VARCHAR NOT NULL,  
    order_buildorder INT,
);

INSERT INTO buildorder (name, order_buildorder) VALUES ('Gateway', 1);
INSERT INTO buildorder (name, order_buildorder) VALUES ('Pylon', 2);

Now I insert without order but the ON INSERT trigger should make it 3 automatically

INSERT INTO buildorder (name) VALUES ('Probe');

But now we update the third buildorder to be in the 1st position

UPDATE buildorder
SET order_buildorder = 1
WHERE name = 'Probe'

All the other rows should now change to reflect this change in ordering. How would I go about writing a trigger for this?

Example result:

[
{name = "Gateway", order_buildorder = 2},
{name = "Pylon", order_buildorder = 3},
{name = "Probe", order_buildorder = 1},
]

Answer

This is a terrible idea.

Instead, define order_buildorder as a real column.

That way, you can always insert a new row to be ordered between two already existing rows without updating existing rows (just take the arithmetic mean of the values between which you want to insert).

If you need an integral order, generate it on the fly using a window function when you select the data.

Advertisement

Answer