CODEWITHSUNDEEP
sqlpostgresql
AJP
AJP

Reputation: 47

PostgreSQL checking dates and raising errors

I have a table car_rental of which I am trying to check each tuple for overlapping dates, and RAISE an EXCEPTION error if these values overlap with my check_date function. But no matter what I do, I keep getting a syntax error on the first line of my function. What am I doing wrong?

CREATE FUNCTION check_date(IN i_sdate DATE, IN i_edate DATE, IN i_plate varchar(10), OUT overlap) 
  SET @i_sdate = start_date(i_sdate);
  SET @i_edate = end_date(i_edate);
  SET @i_plate = plate(i_plate);
  BEGIN
  SET overlap = SELECT CASE WHEN (
      (@i_sdate BETWEEN  start_date AND end_date AND @i_plate = plate FROM car_rental)OR
      (@i_edate BETWEEN start_date AND end_date AND AND @i_plate = plate FROM car_rental)OR
      (start_date BETWEEN @i_sdate AND @i_edate AND AND @i_plate = plate )
      THEN
      RAISE EXCEPTION 'unavailable --> %', plate
            USING HINT = 'Car is unavailable';
      )

Upvotes: 0

Views: 60

Answers (1)

Evan Carroll
Evan Carroll

Reputation: 1

You can easily do this with an EXCLUDE constraint and range types. You don't need a procedural function or triggers.

CREATE TABLE car_rental (
  licplate   text,
  rentdates  daterange,
  PRIMARY KEY (licplate, rentdates),
  EXCLUDE USING gist (licplate WITH =, rentdates WITH &&)
);

INSERT INTO car_rental VALUES
  ('123abc', '[2017-01-05, 2017-01-15]'::daterange),
  ('123abc', '[2017-02-08, 2017-02-10]'::daterange);

INSERT INTO car_rental VALUES
  ('123abc', '[2017-01-10, 2017-01-12]'::daterange);
ERROR:  conflicting key value violates exclusion constraint "car_rental_licplate_rentdates_excl"
DETAIL:  Key (licplate, rentdates)=(123abc, [2017-01-10,2017-01-13)) conflicts with existing key (licplate, rentdates)=(123abc, [2017-01-05,2017-01-16)).

Upvotes: 1

Related Questions