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) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
