PL/pgSQL酒店预订函数问题：日期重叠校验失效致重复预订

你的函数之所以会忽略校验逻辑，核心是两个关键问题：

• 只校验了单条预订记录：原代码里的SELECT check_in_date INTO arrival只会取出该房间的某一条预订记录（默认是第一条），如果房间有其他重叠的预订，完全不会被检测到。
• 日期重叠判断逻辑不全：原逻辑只覆盖了新入住/退房日期落在已有预订时段内的情况，漏掉了新预订完全包含已有预订、或者已有预订完全包含新预订的重叠场景。

以下是修正后的函数：

CREATE OR REPLACE FUNCTION new_booking(room_id_ INTEGER, check_in_date_ DATE, check_out_date_ DATE, comment_ TEXT) 
RETURNS VOID AS $$
BEGIN
    -- 检查该房间是否存在任何与新预订重叠的已有记录
    IF EXISTS (
        SELECT 1 FROM booking 
        WHERE room_id = room_id_
        -- 通用的预订重叠判断逻辑：覆盖所有可能的重叠场景
        AND check_in_date_ < check_out_date
        AND check_out_date_ > check_in_date
    ) THEN
        RAISE EXCEPTION 'This room is not available.';
    ELSE
        INSERT INTO booking(room_id, booking_date, check_in_date, check_out_date, comment)
        VALUES (room_id_, CURRENT_DATE, check_in_date_, check_out_date_, comment_);
    END IF;
END;
$$ LANGUAGE plpgsql;

补充说明 #

1. 用EXISTS子查询可以一次性检查该房间的所有预订记录，只要存在任意一条重叠的就抛出异常，不会再漏掉其他预订。
2. 日期重叠判断逻辑check_in_date_ < check_out_date AND check_out_date_ > check_in_date能覆盖所有重叠场景：
   • 新入住日期在已有预订时段内
   • 新退房日期在已有预订时段内
   • 新预订完全包含已有预订
   • 已有预订完全包含新预订
3. 高并发场景下的额外保障：如果你的系统有大量同时预订的情况，建议给booking表添加排除约束，从数据库层面强制阻止重叠预订（避免函数校验因并发竞态失效）：

   ALTER TABLE booking ADD CONSTRAINT booking_room_date_excl
   EXCLUDE USING gist (
       room_id WITH =,
       daterange(check_in_date, check_out_date) WITH &&
   );
   

内容的提问来源于stack exchange，提问作者SQL_help_needed