From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | ????????? <sheepjxx(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to write a constraint which need to check other table? |
Date: | 2009-09-28 13:06:02 |
Message-ID: | 20090928130602.GE10298@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Mirko Pace | 2009-09-28 13:29:19 | Re: UPDATE statement with syntax error doesn't raise a warning? |
Previous Message | Sam Mason | 2009-09-28 12:00:01 | Re: Newbie's question: How can I connect to my postgresql-server? |