SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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);
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear