0

I have a unique integer column called ordinality. I wrote a function so that when someone wants to edit one row, it will automatically adjust the other ordinality values.

For items (A, B, C, D) with ordinality (1, 2, 3, 4), if we desire D to have ordinality 2, this set the ordinalities to (1, 3, 4, 2).

create rule update_ordinality as (
  on update to
    test_table
  do instead
  update
    test_table
  set ordinality = case
    when ordinality = old.ordinality then new.ordinality
    when ordinality > old.ordinality and ordinality <= new.ordinality then ordinality - 1
    when ordinality >= new.ordinality and ordinality < old.ordinality then ordinality + 1
    else ordinality
  end
);

This function I think only makes sense when updating a singular row. Is there a way to make it so that if it is a bulk update, it proceeds as usual?

blub
  • 582
  • 7
  • 17
  • 1
    Yes use a trigger. Run don't walk away from rules. – Adrian Klaver Aug 20 '21 at 23:19
  • @AdrianKlaver Is there any specific reason you discourage rules so much? – blub Aug 20 '21 at 23:26
  • Because their behavior is opaque. Read through this [Rules](https://www.postgresql.org/docs/current/rules.html) a couple of times and see if you understand what is going on? In particular see the 'Caution' here [Update rules](https://www.postgresql.org/docs/current/rules-update.html). – Adrian Klaver Aug 20 '21 at 23:54
  • 1
    https://stackoverflow.com/a/19466942/905902 <<-- hint – wildplasser Aug 21 '21 at 13:26
  • Rules are **extremely hard** to get right. The point is: you have to **really** understand the rewrite system. (and you'd better not interfere with that!) – wildplasser Aug 21 '21 at 13:29

0 Answers0