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, 1);
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 % 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);
create table hall_seats (
hall int references halls(id),
row int,
seats int,
primary key (hall, row)
);
insert into hall_seats values
(1, 1, 5),(1, 2, 7),(1, 3, 6),(1, 4, 7),
(1, 5, 7),(1, 6, 7),(1, 7, 7),(1, 8, 7),
(1, 9, 6),(1, 10, 7),(1, 11, 10);
create index hall_seats_hall_ix on hall_seats(hall);
create or replace function check_overbooking() returns trigger AS $func$
declare seat_possible boolean := false;
begin
select true into seat_possible
from hall_seats
join film_screenings on film_screenings.hall = hall_seats.hall
where
film_screenings.id = new.screening and
hall_seats.row = new.row and
new.seat between 1 and hall_seats.seats;
if (seat_possible is null or not seat_possible) then
raise exception 'The seat % in row % not exists', new.seat, new.row;
return null;
end if;
return new;
end;
$func$ language plpgsql;
insert into tickets (screening, row, seat, price) values (1, 9, 7, 15);