31 lines
1.1 KiB
PL/PgSQL
31 lines
1.1 KiB
PL/PgSQL
ALTER TABLE product_paint ADD COLUMN visual_order NUMERIC DEFAULT NULL;
|
|
|
|
-- Contraint of visual_order per product_id
|
|
ALTER TABLE product_paint ADD CONSTRAINT visual_order_per_productid UNIQUE (product_id, visual_order);
|
|
|
|
-- Create function that will automatically assign a sequential visual_order to new rows if the visual_order is not provided
|
|
-- in the insert.
|
|
CREATE OR REPLACE FUNCTION enforce_sequential_visual_order()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
max_visual_order INT;
|
|
BEGIN
|
|
-- Get the current max value of visual_order for the given product_id
|
|
SELECT COALESCE(MAX(visual_order), -1) INTO max_visual_order
|
|
FROM product_paint
|
|
WHERE product_id = NEW.product_id;
|
|
|
|
-- Set visual_order to the next sequential number
|
|
NEW.visual_order := max_visual_order + 1;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Activate the trigger
|
|
CREATE TRIGGER enforce_sequential_visual_order
|
|
BEFORE INSERT ON product_paint
|
|
FOR EACH ROW
|
|
WHEN (NEW.visual_order IS NULL) -- Only auto-assign if visual_order is not provided
|
|
EXECUTE FUNCTION enforce_sequential_visual_order();
|