Re: reservation overlap constraint checking?

On Fri, May 20, 2005 at 04:25:08PM +0000, Aditya wrote:
> This must be a classic SQL/RDBMS design problem yet I can't find any good
> "cookbook" descriptions on how best to do it.
>
> The problem is simple, there are a bunch of resources that can be reserved for
> arbitrary time periods and I have to make sure there are no overlapping
> reservations (think hotel rooms, but in this case it is an LCD projector):
[...]

> alter table go_reserve add constraint no_overlaps check (!(select (start_time,
> end_time) overlaps (select start_time, end_time from go_reserve where
> start_time >= now() and status = 'ACTIVE')));
>
> but postgresql doesn't like selects in a constraint...

so it turns out the best I can come up with is to use a function that returns
a boolean since that works fine in a table constraint:

CREATE OR REPLACE FUNCTION "go_reserve_no_overlap" (integer, timestamp,
timestamp, text)
RETURNS boolean AS '
select
case when count(*) > 0 then false else true end
from
go_reserve
where
(($2, $3) overlaps (start_time,end_time) = true)
and resource = $1
and status = $4;
' LANGUAGE 'sql';

alter
table go_reserve
add constraint
no_overlaps check (go_reserve_no_overlap(resource::integer,
start_time::timestamp, end_time::timestamp, 'ACTIVE'));

I'm all ears if someone has a more elegant solution.

Thanks,
Adi