I'm implementing a car rental system and I'm trying to build a query that checks the slot time before inserting a new booking.
Instead of making different querys, I want to do transactions but I don't know how. I would do something like this:
SELECT car.plate_nr,model,passenger,manual,type,id_parkin g
FROM car LEFT JOIN booking on car.plate_nr = booking.plate_nr
WHERE (start_date, end_date) OVERLAPS (timestamp '2010-05-25 8:00',timestamp '2010-05-25 15:00')
if the previous query returns 0 then do
INSERT INTO booking (plate_nr, start_date, end_date, destination, username) RETURNING id_booking;
You don't really need anything complex. You can do this with a single sql statement.
INSERT INTO booking(...)
FROM car c
FROM booking b
WHERE c.plate_nr = b.plate_nr
AND (b.start_date, b.end_date) OVERLAPS (timestamp '2010-05-25 8:00',timestamp '2010-05-25 15:00')
) IS NULL
Oh, and you probably want to use timestamptz unless you live somewhere that doesn't have daylight savings time, all of your locations are w/in a single timezone and your company will never expand to span multiple timezones.