Thank you very much, I think I need to stady more about trigger. 2009/9/28 A. Kretschmer <andreas.kretsch...@schollglas.com>
> In response to ????????? : > > Yes, you are right. That maybe a bad example. what I want to say maybe > like > > this: > > > > create table a ( > > id integer, > > room varchar(32), > > start time, > > end time, > > PRIMARY KEY(id) > > ) > > How can I check if it is the same room, when I insert the data, the start > time > > and end time doesn't overlap? > > Please answer to the list, okay? > > test=*# create table a ( id int primary key, room char(32), start_time > timestamp, end_time timestamp); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for > table "a" > CREATE TABLE > test=*# create or replace function check_overlapp() returns trigger as > $$begin perform id from a where room=new.room and (start_time,end_time) > overlaps (new.start_time,new.end_time); if found then raise notice 'room > reserved'; return null; else return new; end if; end; $$language plpgsql; > CREATE FUNCTION > test=*# create trigger trg_check before insert or update on a for each row > execute procedure check_overlapp(); > CREATE TRIGGER > test=*# insert into a values (1, 'room1', '2009-10-01'::timestamptz, > '2009-10-10'::timestamptz); > INSERT 0 1 > test=*# insert into a values (2, 'room1', '2009-09-01'::timestamptz, > '2009-09-10'::timestamptz); > INSERT 0 1 > test=*# insert into a values (3, 'room1', '2009-09-08'::timestamptz, > '2009-09-20'::timestamptz); > NOTICE: room reserved > INSERT 0 0 > > You can also use RAISE EXCEPTION to force an error: > > test=*# create or replace function check_overlapp() returns trigger as > $$begin perform id from a where room=new.room and (start_time,end_time) > overlaps (new.start_time,new.end_time); if found then raise exception 'room > reserved'; return null; else return new; end if; end; $$language plpgsql; > CREATE FUNCTION > test=*# insert into a values (3, 'room1', '2009-09-08'::timestamptz, > '2009-09-20'::timestamptz); > ERROR: room reserved > test=!# > > > > HTH, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) >
