SQLize Online / PHPize Online  /  SQLtest Online

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);