PostgreSQL checking dates and raising errors

Question

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

It seems that you are trying to use MS SQL Server TSQL for PostgreSQL. Try to start here, continue here. PS If you mention an error - provide its text please. — Abelisto
– Abelisto, Commented Apr 8, 2017 at 22:11
@Abelisto . . . This isn't quite SQL Server syntax. I see elements of SQL Server, MySQL and Oracle, I think. — Gordon Linoff
– Gordon Linoff, Commented Apr 8, 2017 at 22:13
@GordonLinoff Hmm... Yes, it will be interesting to know the origin of this code. However, as for me, set @something = ... is the sign of TSQL (but without semicolon as I remember). — Abelisto
– Abelisto, Commented Apr 8, 2017 at 22:19
Thanks guys. I'm new to SQL and I guess this is a result of mashing together 3 different SQL languages... I will start again from scratch using the documentation @Abelisto provided. — AJP
– AJP, Commented Apr 8, 2017 at 22:26
@AJP If you want to raise custom exception - PL/pgSQL is your way. In particular, about exceptions and others. Good luck in your learning! — Abelisto
– Abelisto, Commented Apr 8, 2017 at 22:32

Evan Carroll · Accepted Answer · 2017-04-08 22:32:41Z

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

answered Apr 8, 2017 at 22:32

Evan Carroll

1

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

2 Comments

AJP Over a year ago

Thank you for the suggestion, but I need to write this in a function, which I am having quite a bit of trouble understanding unfortunately.

Evan Carroll Over a year ago

It will be substantially slower, why would you want to do that? And, are you using daterange?

Collectives™ on Stack Overflow

PostgreSQL checking dates and raising errors

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related