create table halls (
id serial primary key,
name text,
seats_count int,
screen_type text check (screen_type in ('regular', 'stereo'))
);
insert into halls (name, seats_count, screen_type) values ('Blue', 25, 'regular');
insert into halls (name, seats_count, screen_type) values ('Gold', 5, 'stereo');
select * from halls;
create table films (
id serial primary key, -- первичный ключ
title text, -- название фильма
description text, -- краткое описание, сюжет
release_year int, -- год выхода на экран
duration int, -- длительность в минутах
director int,
genre int
);
create table persons (
id serial primary key,
name text,
year_of_birth int
);
alter table films
add constraint film_director_fk
foreign key (director) references persons(id);
insert into films (title) values ('Very good film');
create table film_screenings (
id serial primary key,
film int references films(id), -- фильм
hall int references halls(id), -- кинозал
start_at timestamp, -- дата и время начала сеанса
price numeric -- цена билета
);
insert into film_screenings (film, hall) values (1, 2);
create table tickets (
id uuid primary key default uuid_generate_v4(),
screening int references film_screenings(id), -- сеанс
row smallint, -- ряд
seat smallint, -- место
price numeric -- цена
);
alter table film_screenings rename column price TO recomended_price;
create unique index ticket_seat_unique on tickets (screening, row, seat);
create function check_overbooking() returns trigger AS $$
declare hall text;
declare hall_seats_count int;
declare tickets_sold int;
begin
select halls.name, seats_count into hall, hall_seats_count
from halls
join film_screenings on film_screenings.hall = halls.id
where film_screenings.id = new.screening;
select count(*) into tickets_sold from tickets where screening = new.screening;
if (tickets_sold >= hall_seats_count) then
raise exception 'The hall %d is full', hall;
return null;
end if;
return new;
end;
$$ language plpgsql;
create trigger check_overbooking
before insert on tickets
for each row
execute function check_overbooking();
insert into tickets (screening, row, seat, price) values
(1, 1, 1, 10), (1, 1, 2, 10), (1, 1, 3, 10), (1, 1, 4, 10), (1, 1, 5, 10);
select * from tickets;
insert into tickets (screening, row, seat, price) values
(1, 1, 1, 10);