|
Hi!
>Would
you be able to adapt this to your needs?: Thank
you very much.
Great
solution. I
refactored it as shown below. Query
returns only dates for single day. Changing limit clause to 300 does not return
next day. How
to return other day dates also, excluding sundays and public holidays in pyha
table ? Andrus. Testcase
is: create table pyha (pyha date primary key);
insert into pyha(pyha) values('2014-10-29');
create table yksus2(yksus char(10) primary key);
insert into yksus2 values ('JOHN'),('MARY');
CREATE EXTENSION btree_gist;
CREATE TABLE reservat
(
reservat_id serial primary key,
objekt2 char(10) not null references yksus2
on update cascade deferrable,
during tstzrange not null,
EXCLUDE USING gist (objekt2 WITH =, during WITH &&),
CONSTRAINT same_date
CHECK (lower(during)::date =
upper(during)::date),
CONSTRAINT max_1month_future
CHECK (lower(during) between current_date and
current_date+ interval'1 month' ),
CONSTRAINT time_between_1000_and_2100
CHECK (lower(during)::time >= '10:00'::time and
upper(during)::time < '21:00'::time),
CONSTRAINT lower_bound_included
CHECK (lower_inc(during)),
CONSTRAINT upper_bound_excluded
CHECK (not upper_inc(during)),
CONSTRAINT start_time_at_15minute_offset
CHECK (EXTRACT(MINUTE FROM lower(during)) IN (0,
15, 30,45)),
-- or (extract(epoch from lower(during)::time)::int %
(60*15) = 0)
CONSTRAINT end_time_at_15minute_offset
CHECK (EXTRACT(MINUTE FROM upper(during)) IN (0,
15, 30,45)),
CONSTRAINT duration_between_15min_and_4hours
CHECK (upper(during) - lower(during) between '15
mins'::interval and '4 hours'::interval),
CONSTRAINT exclude_sundays
CHECK (date_part('dow', lower(during)) in
(1,2,3,4,5,6) )
);
create or replace function holiday_check() returns trigger language plpgsql
stable as $$
begin
if exists (select * from
pyha where pyha between lower(NEW.during)::date and
upper(NEW.during)::date) then
raise
exception 'public holiday %', lower(NEW.during) ;
else
return
NEW;
end if;
end;
$$;
create trigger holiday_check_i before insert or update on Reservat for each
row execute procedure holiday_check();
INSERT INTO reservat (objekt2, during)
VALUES ('MARY','[2014-10-28 11:30+2,2014-10-28
13:00+2)'::tstzrange);
INSERT INTO reservat (objekt2, during)
VALUES ('JOHN','[2014-10-28 10:00+2,2014-10-28
11:30+2)'::tstzrange);
SELECT yksus2.yksus, times.period
FROM generate_series('2014-10-28 10:00+2'::timestamptz, '2014-10-28
21:00+2', '15 mins'::interval) times(period)
CROSS JOIN yksus2
LEFT JOIN reservat ON tstzrange(times.period,times.period + '1 hour 30
mins'::interval, '[)') && reservat.during AND yksus2.yksus =
reservat.objekt2
WHERE reservat.during IS NULL
ORDER BY 2, 1
LIMIT 300;
|
