Postgres stored procedure(function) confusion

I’m pretty new to Postgres and SQL as a whole and could use a bit of help with a function here.

I have this table:

CREATE TABLE car_rentals(
plate       varchar(10)     NOT NULL,
start_date  date            NOT NULL,
end_date    date            NOT NULL,
license_nr  varchar(10)     NOT NULL,
CONSTRAINT unq_car_rentals_start UNIQUE (plate, start_date),
CONSTRAINT unq_car_rentals_end UNIQUE (plate, end_date));

​x
 
CREATE TABLE car_rentals(plate       varchar(10)     NOT NULL,start_date  date            NOT NULL,end_date    date            NOT NULL,license_nr  varchar(10)     NOT NULL,CONSTRAINT unq_car_rentals_start UNIQUE (plate, start_date),CONSTRAINT unq_car_rentals_end UNIQUE (plate, end_date));​

What i need is for a function to take as input plate, start date and end date and throw an error if the table contains any row with the same plate where the rental period is different from but overlaps with the given period.

I found this document on reporting errors but I’m not sure how to properly format the function in order to implement what I need. https://www.postgresql.org/docs/9.6/plpgsql-errors-and-messages.html

Answer

The WHERE condition of a query you could use in a trigger could be:

WHERE daterange(start_date, end_date, '[]') && daterange($1, $2, '[]')  -- overlaps
  AND (start_date <> $1 OR end_date <> $2)  -- is not equal
  AND plate = $3

 
WHERE daterange(start_date, end_date, '[]') && daterange($1, $2, '[]')  -- overlaps  AND (start_date <> $1 OR end_date <> $2)  -- is not equal  AND plate = $3​

Here $1, $2 and $3 are placeholders for the data against which you want to check, and the query becomes simple by using the “overlaps” range operator &&.

Advertisement

Answer