2

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},
]
Legion
  • 199
  • 2
  • 12

1 Answers1

4

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks this is an intriguing solution. I will probably do this. I'm also exploring the possibility of using which item comes before the current one. Any reason you chose for this solution? – Legion Oct 02 '19 at 11:00
  • Yes, because you can always find a `double precision` number between two given ones, so you can insert values everywhere without having to rejiggle everything. PostgreSQL enums are implemented that way. – Laurenz Albe Oct 02 '19 at 11:01
  • Makes sense I decided to do your solution. Thanks – Legion Oct 02 '19 at 13:15
  • 1
    Why `double precision` (8 bytes) and not `real` (4 bytes)? 6 decimals digits precision is not enough? – ManUtopiK Sep 23 '20 at 22:31
  • 1
    @ManUtopiK You are right, `real` is better. I modified my answer. – Laurenz Albe Sep 24 '20 at 02:18
  • This has its limits of course. `REAL` is an approximate type, so at one time you'll add two different numbers, divide by two, but end up with one of those two numbers again. https://dbfiddle.uk/?rdbms=postgres_13&fiddle=67aea032384c0512fe5bf96c554dc703. (With `double precision` instead of `real`you can insert twice as many rows before hitting the precision limit.) – Thorsten Kettner Jul 12 '21 at 07:25
  • @ThorstenKettner Right. If you need even more precision (and don't mind the "wasted" storage), you would use `numeric`. – Laurenz Albe Jul 12 '21 at 07:50