Postgres stored procedure(function) confusion

Question

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));

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

You don't need a function you need this Range Constraint. Look at btree_gist example and adapt to your table. — Adrian Klaver
– Adrian Klaver, Commented Apr 6, 2021 at 22:47
You want a trigger that fires before insert and throws an exception if there’s an overlap. — Bohemian
– Bohemian ♦, Commented Apr 7, 2021 at 7:25

user330315 · Accepted Answer · 2021-04-07 08:26:04Z

You don't need a trigger to achieve this. Postgres supports so called exclusion constraints exactly for this purpose:

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 no_overlapping_ranges 
     exclude using gist (plate with =, daterange(start_date, end_date, '[]') with &&)
);

The constraint definition in details:

plate with = for rows with the same plate number
daterange(start_date, end_date, '[]') with && - fail if there are rows with an overlapping range.

As the constraint includes the = operator, you need to install the extension btree_gist in order to create the above constraint:

As a superuser run:

create extension btree_gist;

in the database where you want to create the car_rentals database (you only need to do this once).

Online example

Laurenz Albe · Accepted Answer · 2021-04-07 08:19:30Z

1

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

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 &&.

answered Apr 7, 2021 at 8:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Postgres stored procedure(function) confusion

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related