sql/Pg-upgrade2/shop_images_dont_skip_positon.sql

   1 -- @tag: shop_images_dont_skip_position
   2 -- @description: Keine Lücken in Position bei Shopbilder
   3 -- @depends: release_3_9_0
   4
   5 UPDATE shop_images
   6 SET position = reordered.new_position
   7 FROM (
   8   SELECT id, rank() OVER (PARTITION BY object_id ORDER BY position ASC) AS new_position
   9   FROM shop_images
  10 ) reordered
  11 WHERE shop_images.id = reordered.id
  12 AND shop_images.position IS DISTINCT FROM reordered.new_position;
  13
  14 CREATE OR REPLACE FUNCTION shop_images_reorder_position()
  15 RETURNS TRIGGER
  16 AS
  17 $$
  18 BEGIN
  19   UPDATE shop_images
  20   SET position = reordered.new_position
  21   FROM (
  22     SELECT id, rank() OVER (PARTITION BY object_id ORDER BY position ASC) AS new_position
  23     FROM shop_images
  24     WHERE shop_images.object_id = OLD.object_id
  25   ) reordered
  26   WHERE shop_images.id = reordered.id
  27   AND shop_images.position IS DISTINCT FROM reordered.new_position;
  28
  29   RETURN OLD;
  30 END;
  31 $$
  32 LANGUAGE plpgsql;
  33
  34 CREATE TRIGGER after_delete_shop_images_trigger
  35 AFTER DELETE ON shop_images
  36 FOR EACH ROW
  37 EXECUTE FUNCTION shop_images_reorder_position();