Search Postgresql Archives

Re: How to write a constraint which need to check other table?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux