只允许预约表中的工作时间 [英] Allow only work time in reservations table

问题描述

PostgreSql 9.2保留表定义为

PostgreSql 9.2 Reservation table is defined as

CREATE EXTENSION btree_gist;
CREATE TABLE schedule (
  id serial primary key,
  during tsrange not null,
  EXCLUDE USING gist (during WITH &&)
);

假期列在表

CREATE TABLE holiday ( day primary key );

上班时间为8至18:00，预定时间为30分钟只要。
如何在工作时间内增加限制，以便只允许在工作时间进行预约：

Work hours are from 8 to 18:00 in work days and reservatons can be done by 30 minute intervals only. How to add constraints to during values so that it allows only reservations during work time:

1. tsrange中的开始和结束日期是


2. 开始和结束日期不能是星期六和星期日


3. 开始和结束日期不能出现在公众假期表


4. 开始时间只有8:00，8:30，9:00，9:30，... 16:00，16:30，17:00或17:30，包含


5. 结束时间只有8:30，9:00，9:30，... 16:00，16:30，17:00，17:30或18:00独家

1. Start and end dates in tsrange are always same.
2. Start and end dates cannot be saturday and sunday
3. Start and end dates cannot appear in public holiday table
4. Start time can be only 8:00 , 8:30, 9:00, 9:30, ... 16:00, 16:30, 17:00 or 17:30 inclusive
5. End time can be only 8:30, 9:00, 9:30, ... 16:00, 16:30, 17:00, 17:30 or 18:00 exclusive

将这些或某些约束添加到此表中是否合理？
如果是，如何添加？
调度表结构可以改变，如果这有帮助。

Is it reasonable to add those or some of those constraints to this table ? If yes, how to add ? schedule table structure can changed if this helps.

推荐答案

您需要更改表格定义并添加一些检查约束：

You need to change you table definition and add some check constraints:

CREATE TABLE schedule (
  id serial primary key,
  during tsrange not null check(
    (lower(during)::date = upper(during)::date) and 
    (date_trunc('hour', upper(during)) + INTERVAL '30 min' * ROUND(date_part('minute', upper(during)) / 30.0) = upper(during)) and
    (date_trunc('hour', lower(during)) + INTERVAL '30 min' * ROUND(date_part('minute', lower(during)) / 30.0) = lower(during)) and
    (lower(during)::time >= '8:00'::time and upper(during)::time <= '18:00'::time) and
    (date_part('dow', lower(during)) in (1,2,3,4,5) and date_part('dow', upper(during)) in (1,2,3,4,5))
  ),
  EXCLUDE USING gist (during WITH &&)
);

支票按此顺序

• 开始和结束日是相同的


• 开始/结束可以在30分钟边界


• 之间和8： 00 .. 18:00


• 只有平日

• start and end day are the same
• start / end can must be on 30 min boundary
• and between 8:00 .. 18:00
• only weekdays

我们需要假期表：
插入假期值（'2012-11-28'）;

We need something in holiday table: insert into holiday values ('2012-11-28');

检查不能引用其他表，因此我们需要触发函数（可能更好的是将所有检查到这个函数，即将它们放在一个位置）：

check can not reference other table hence we need trigger function (it might be better to put all check into this function i.e. have them at one place):

create function holiday_check() returns trigger language plpgsql stable as $$
begin
    if exists (select * from holiday where day in (lower(NEW.during)::date, upper(NEW.during)::date)) then
        raise exception 'public holiday';
    else
        return NEW;
    end if;
end;
$$;

然后我们需要在 insert 之前创建触发器/ 更新：

Then we need to create triggers before insert/update:

create trigger holiday_check_i before insert on schedule for each row execute procedure holiday_check();
create trigger holiday_check_u before update on schedule for each row execute procedure holiday_check();

最后，一些测试：

-- OK
insert into schedule(during) values (tsrange('2012-11-26 08:00', '2012-11-26 09:00'));
INSERT 0 1

-- out of business hours
insert into schedule(during) values (tsrange('2012-11-26 04:00', '2012-11-26 05:00'));
ERROR:  new row for relation "schedule" violates check constraint "schedule_during_check"
DETAIL:  Failing row contains (12, ["2012-11-26 04:00:00","2012-11-26 05:00:00")).

-- End time can be only 8:30, 9:00, 9:30, ... 16:00, 16:30, 17:00, 17:30 or 18:00 exclusive
insert into schedule(during) values (tsrange('2012-11-26 08:00', '2012-11-26 09:10'));
ERROR:  new row for relation "schedule" violates check constraint "schedule_during_check"
DETAIL:  Failing row contains (13, ["2012-11-26 08:00:00","2012-11-26 09:10:00")).

-- Start time can be only 8:00 , 8:30, 9:00, 9:30, ... 16:00, 16:30, 17:00 or 17:30 inclusive
insert into schedule(during) values (tsrange('2012-11-26 11:24', '2012-11-26 13:00'));
ERROR:  new row for relation "schedule" violates check constraint "schedule_during_check"
DETAIL:  Failing row contains (14, ["2012-11-26 11:24:00","2012-11-26 13:00:00")).

-- holiday
insert into schedule(during) values (tsrange('2012-11-28 10:00', '2012-11-28 13:00'));
ERROR:  public holiday

这篇关于只允许预约表中的工作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！