PSQL funkcja sprawdzająca date i czas podczas zapisu na zajęcia

Question

Tworzę bazę danych która ma umożliwiać zapisy na zajęcia, utknąłem przy sprawdzaniu daty i czasu podczas zapisywania studenta na dany kurs czy nie nakładają mu się jakiekolwiek course_section z danego course

Cała baza wygląda tak:

CREATE TABLE "semester" (
	"semester_id" serial primary key,
	"semester_describtion" varchar);
	
	
CREATE TABLE "course" (
	"c_id" serial not null,
	"c_name" varchar NOT NULL,
	"c_describtion" varchar,
	"semester_id" int,
	"startDate" DATE,
	"endDate" DATE,
	PRIMARY KEY (c_id, semester_id),
	FOREIGN KEY (semester_id) references semester(semester_id));


CREATE TABLE "staff" (
	"staff_id" serial primary key,
	"staff_lastname" varchar(40) NOT NULL,
	"staff_firstname" varchar(40) NOT NULL,
	"staff_email" varchar(40));

	
CREATE TABLE "student" (
	"s_id" serial PRIMARY KEY,
	"s_lastname" varchar(40) NOT NULL,
	"s_firstname" varchar(40) NOT NULL,
	"semester_id" int default '1',
	foreign key (semester_id) references semester(semester_id));

	
CREATE TABLE "location" (
	"location_id" serial,
	"room_nr" int,
	"building_id" varchar(40) DEFAULT 'D5',
	"max_students" int default '10',
	primary key (location_id, max_students));



CREATE TABLE "course_section" (
    "c_section_id" serial,
    "c_id" int NOT NULL,
    "semester_id" int NOT NULL,
    "staff_id" int,
    "location_id" int not null,
    "c_section_date" DATE,
    "c_section_time" TIME default '08:00',
    "c_section_during" TIME default '01:30',
    "c_section_type" TEXT not null,
    "max_students" int default '10',
    "students" int default '0',
    check(c_section_type = 'L' OR c_section_type = 'W' OR c_section_type = 'C' OR c_section_type = 'I'),
    primary key (c_id, staff_id, location_id,c_section_date,c_section_time),
    foreign key (c_id, semester_id) references course(c_id, semester_id),
    foreign key (staff_id) references staff(staff_id),
    foreign key (location_id, max_students) references location(location_id, max_students)
    );

	
	
CREATE TABLE "enrollment" (
	"s_id" int not null,
	"mark" int,
	"c_id" int not null,
	"semester_id" int not null,
	primary key (s_id,c_id,semester_id),
	foreign key (s_id) references student(s_id),
	foreign key (c_id, semester_id) references course(c_id, semester_id));

A tutaj jest funkcja sprawdzająca datę i czas:

create function check_date() returns trigger as '
declare
date1 date;
time1 time;
begin

Select c_section_date, c_section_time into date1, time1 from enrollment e 
inner join course c using (c_id)
inner join course_section cs using (c_id);

IF EXISTS  (select e.s_id , cs.c_section_date, cs.c_section_time from enrollment e 
inner join course c using (c_id)
inner join course_section cs using (c_id)
where new.s_id=e.s_id AND date1=c_section_date and time1 = c_section_time) THEN
RAISE NOTICE  ''Zajecia nakladaja sie'';
Return null;
ELSE
return new;
END IF;
END;
' language 'plpgsql';

create trigger t_check_date before insert on enrollment for each row execute procedure check_date();

select e.s_id , cs.c_section_id, cs.c_id, cs.c_section_date, cs.c_section_time from enrollment e
inner join course c using (c_id)
inner join course_section cs using (c_id);

insert into semester values ('1','opis semestru');
insert into student (s_lastname,s_firstname, semester_id) values ('Jacek','Sznycel1','1');
insert into staff (staff_lastname,staff_firstname,staff_email) values ('Ledy w ','Halogenach','ogarniam@tablice.pl');

insert into location (location_id,max_students,room_nr) values ('1','1','101');
insert into location (location_id,max_students,room_nr) values ('2','2','102');
insert into location (location_id,max_students,room_nr) values ('3','3','103');

insert into course (c_name,c_describtion,semester_id) values ('SQL','bardzo warzne','1');
insert into course (c_name,c_describtion,semester_id) values ('SQL2','bardzo warzne','1');
insert into course (c_name,c_describtion,semester_id) values ('SQL3','bardzo warzne','1');

insert into course_section (c_id,semester_id,staff_id,location_id,c_section_type,max_students,c_section_date) values ('1','1','1','3','L','3','2017-01-01');
insert into course_section (c_id,semester_id,staff_id,location_id,c_section_type,max_students,c_section_date) values ('2','1','1','3','L','3','2017-01-01');
insert into course_section (c_id,semester_id,staff_id,location_id,c_section_type,max_students,c_section_date,c_section_time) values ('3','1','1','3','L','3','2017-01-01','09:35');

insert into enrollment (s_id,c_id,semester_id) values ('1','1','1');
insert into enrollment (s_id,c_id,semester_id) values ('1','2','1');
--------------tu powinien być raise notice
insert into enrollment (s_id,c_id,semester_id) values ('1','3','1');

Gdy zapisuje studenta na zajęcia (enrollment) po raz pierwszy wszystko jest ok, potem za każdym razem wyskakuje RAISE NOTICE niezależnie od tego czy c_section_time się różni czy nie.

Dodam, że jest to mój pierwszy projekt z baz danych więc ewentualne uwagi odnośnie pisania kodu mile widziane :))

Answer 1

Jakby ktoś wpadł na podobny problem, tutaj rozwiązanie ze StackOverflow:

 

CREATE OR REPLACE FUNCTION check_date() 
  RETURNS trigger AS
$func$
BEGIN
   IF EXISTS (
      SELECT 1
      FROM   course_section cs1
      WHERE  cs1.c_id        = NEW.c_id
      AND    cs1.semester_id = NEW.semester_id
      AND EXISTS (
         SELECT 1
         FROM   enrollment e 
         JOIN   course_section cs USING (c_id, semester_id)
         WHERE  e.semester_id      = NEW.semester_id
         AND    e.s_id             = NEW.s_id
         AND    cs.c_section_date  = cs1.c_section_date
         AND    cs.c_section_time  = cs1.c_section_time))  THEN

      RAISE NOTICE 'You have classes at that time';
      RETURN null;
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

PS: działa tak samo jak funkcja sprawdzająca datę i czas, którą podałem u góry.

PS2: czyli źle, mogę zapisać studenta tylko na jeden kurs