How to write postgresql trigger that will check inserted values

Question

I have an issue that I can`t figure out. I have prepared table that will store some data of delivery prices.

CREATE TABLE delivers_prices(

        id              SERIAL            PRIMARY KEY
,       price           NUMERIC(40,2)     NOT NULL
,       weight_from     NUMERIC(20)       NOT NULL  
,       weight_to       NUMERIC(20)       NOT NULL     
,       zone            INTEGER               NULL      REFERENCES zones (id) ON UPDATE CASCADE ON DELETE CASCADE
,       deliver         INTEGER           NOT NULL      REFERENCES delivers (id) ON UPDATE CASCADE ON DELETE CASCADE

,       CHECK(weight_from < weight_to)

);

The issue is that I need to constraint two fields weight_from and weight_to

algorithm need to check if currently inserting row will not be in the range between weight_from and weight_to of already inserted rows. More precisely there cant be situation when price of delivery (row) is twice for deliver in specified range

I already implemented the trigger but they not working as I expected

CREATE FUNCTION delivers_prices_unique( ) RETURNS TRIGGER AS
$func$
    DECLARE weightFrom NUMERIC(20);
    DECLARE weightTo NUMERIC(20);
    DECLARE deliverId INTEGER;
    BEGIN
        CASE
            WHEN TG_OP = 'DELETE' THEN
                        weightFrom := OLD.weight_from;
                        weightTo := OLD.weight_to;
                        deliverId := OLD.deliver;
            ELSE
                        weightFrom := NEW.weight_from;
                        weightTo := NEW.weight_to;
                        deliverId := NEW.deliver;
        END CASE;

        IF 
                EXISTS (
                    SELECT * FROM delivers_prices oth
                    WHERE oth.deliver = deliverId
                    AND oth.weight_from BETWEEN weightFrom AND weightTo
                    OR oth.weight_to BETWEEN weightFrom AND weightTo
                )
                THEN
                    RAISE EXCEPTION 'delivery price for given weight exist';
                    RETURN NULL;
        ELSE
            RETURN NEW;
        END IF;
    END;
$func$ LANGUAGE 'plpgsql';

usage of function

 CREATE TRIGGER check_delivers_prices
    BEFORE UPDATE OR INSERT OR DELETE
    ON delivers_prices
    FOR EACH ROW
    EXECUTE PROCEDURE delivers_prices_unique();

and some examples:

INSERT INTO delivers_prices(price, weight_from, weight_to, deliver)
VALUES (22, 20, 100, 1); // we inserting the range 20 - 100

so from now delivery of some products between 20 and 100 kg will cost 22 EUR

INSERT INTO delivers_prices(price, weight_from, weight_to, deliver)
VALUES (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.

Why trigger not throw exception beacuse

       SELECT * FROM delivers_prices oth
       WHERE oth.deliver = 1
       AND oth.weight_from BETWEEN 21 AND 22
       OR oth.weight_to BETWEEN 21 AND 22

I know something wrong with SELECT in trigger in If clause. I will be grateful for and suggestions or help

score 1 · Accepted Answer · 2018-01-16 13:06:48Z

1

No trigger required, you can do this with an exclusion constraint

alter table delivers_prices 
   add constraint unique_weight_range 
   exclude using gist (numrange(weight_from, weight_to, '[]') with &&);

The range used will include the bounds, so if there is a range 20-100, you can't insert a range e.g. from 15-20. But this can be changed in the definition of the constraint.

You can apply the check for each delivery_id.

alter table delivers_prices 
   add constraint unique_weight_range 
   exclude using gist (delivery with =, numrange(weight_from, weight_to, '[]') with &&);

This means that the overlapping ranges are only checked for rows with the same deliver value.

However you need to install the extension btree_gist in order to use integer values in a GiST index.

edited Jan 16, 2018 at 13:06

answered Jan 15, 2018 at 20:00

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Michał Ziembiński Over a year ago

it is almost what I need but range should work for each deliver. So constraint should contains also deliver id field

user330315 Over a year ago

@MichałZiembiński: see my edit. You can include the deliver column in the constraint

404 · Accepted Answer · 2018-01-15 18:15:50Z

Firstly it's not entirely clear to me whether you want to check whether the record you're inserting is entirely contained within some existing row - for example, existing row from/to: (20, 100), new row (21, 22), new row is wholly contained within existing row - or whether you're just looking for some sort of overlap (e.g. existing row (20, 100), new row (19, 22), overlap found).

Either way your EXISTS query is broken because you are missing brackets around the parts of the OR statement. As it is, a match on delivery id is optional. Due to using OR, I assume you're happy to raise an exception on a range overlap. So:

SELECT TRUE
FROM delivers_prices
WHERE deliver = deliverId
AND (weightFrom BETWEEN weight_from AND weight_to
    OR weightTo BETWEEN weight_from AND weight_to
)

Currently you're checking whether the existing record starts or ends inside the new range. I assume what you really want is to check whether the new range starts or ends inside the existing range.

It's also worth pointing out a couple other issues with the trigger: it's pointless returning NULL after raising an exception, as that code will never execute; and use snake_case for variable names.

Collectives™ on Stack Overflow

How to write postgresql trigger that will check inserted values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related