Skip site navigation (1) Skip section navigation (2)

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

From: 纪晓曦 <sheepjxx(at)gmail(dot)com>
To: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How to write a constraint which need to check other table?
Date: 2009-09-28 14:03:18
Message-ID: 37a11ce00909280703t85e9352jf332ddb850dcf58@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Thank you very much, I think I need to stady more about trigger.

2009/9/28 A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)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)
>

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2009-09-28 14:22:17
Subject: Re: postgresql error
Previous:From: Mirko PaceDate: 2009-09-28 13:29:19
Subject: Re: UPDATE statement with syntax error doesn't raise a warning?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group