PNRNnhKW

1CREATE TABLE delivers_prices(
2
3        id              SERIAL            PRIMARY KEY
4,       price           NUMERIC(40,2)     NOT NULL
5,       weight_from     NUMERIC(20)       NOT NULL  
6,       weight_to       NUMERIC(20)       NOT NULL     
7,       zone            INTEGER               NULL      REFERENCES zones (id) ON UPDATE CASCADE ON DELETE CASCADE
8,       deliver         INTEGER           NOT NULL      REFERENCES delivers (id) ON UPDATE CASCADE ON DELETE CASCADE
9
10,       CHECK(weight_from < weight_to)
11
12);
13	
14CREATE FUNCTION delivers_prices_unique( ) RETURNS TRIGGER AS
15$func$
16    DECLARE weightFrom NUMERIC(20);
17    DECLARE weightTo NUMERIC(20);
18    DECLARE deliverId INTEGER;
19    BEGIN
20        CASE
21            WHEN TG_OP = 'DELETE' THEN
22                        weightFrom := OLD.weight_from;
23                        weightTo := OLD.weight_to;
24                        deliverId := OLD.deliver;
25            ELSE
26                        weightFrom := NEW.weight_from;
27                        weightTo := NEW.weight_to;
28                        deliverId := NEW.deliver;
29        END CASE;
30
31        IF 
32                EXISTS (
33                    SELECT * FROM delivers_prices oth
34                    WHERE oth.deliver = deliverId
35                    AND oth.weight_from BETWEEN weightFrom AND weightTo
36                    OR oth.weight_to BETWEEN weightFrom AND weightTo
37                )
38                THEN
39                    RAISE EXCEPTION 'delivery price for given weight exist';
40                    RETURN NULL;
41        ELSE
42            RETURN NEW;
43        END IF;
44    END;
45$func$ LANGUAGE 'plpgsql';
46	
47CREATE TRIGGER check_delivers_prices
48    BEFORE UPDATE OR INSERT OR DELETE
49    ON delivers_prices
50    FOR EACH ROW
51    EXECUTE PROCEDURE delivers_prices_unique();
52	
53INSERT INTO delivers_prices(price, weight_from, weight_to, deliver)
54VALUES (22, 20, 100, 1); // we inserting the range 20 - 100
55	
56INSERT INTO delivers_prices(price, weight_from, weight_to, deliver)
57VALUES (22, 21, 22, 1); // should not be inserted beacuse we already have row with range 20 - 100 and this row will be between that range.
58	
59SELECT * FROM delivers_prices oth
60       WHERE oth.deliver = 1
61       AND oth.weight_from BETWEEN 21 AND 22
62       OR oth.weight_to BETWEEN 21 AND 22