PostgresStudy2025/homework/1.sql

DO $$ 
begin

-- Создаем таблицу званий
if exists(select from pg_catalog.pg_tables where tablename = 'ranks') then
raise notice 'Ranks table exists';
else 
create table ranks (id serial primary key, name varchar(32));
end if;

-- Создаем таблицу пользователей
if exists(select from pg_catalog.pg_tables where tablename = 'users') then
raise notice 'Users table exists';
else
create table users (id serial primary key, name varchar(64), rank_id integer references ranks(id));
end if;

-- Заполняем звания
if exists(select from ranks where name = 'Рядовой') then
raise notice 'Ranks values exist';
else
insert into ranks(name) values ('Рядовой');
insert into ranks(name) values ('Ефрейтор');
insert into ranks(name) values ('Младший сержант');
insert into ranks(name) values ('Сержант');
insert into ranks(name) values ('Старший сержант');
insert into ranks(name) values ('Старшина');
insert into ranks(name) values ('Прапорщик');
insert into ranks(name) values ('Старший прапорщик');
insert into ranks(name) values ('Младший лейтенант');
insert into ranks(name) values ('Лейтенант');
insert into ranks(name) values ('Старший лейтенант');
insert into ranks(name) values ('Капитан');
insert into ranks(name) values ('Майор');
insert into ranks(name) values ('Подполковник');
insert into ranks(name) values ('Полковник');
insert into ranks(name) values ('Генерал майор');
insert into ranks(name) values ('Генерал лейтенант');
insert into ranks(name) values ('Генерал полковник');
insert into ranks(name) values ('Генерал армии');
insert into ranks(name) values ('Маршал России');
end if;

-- Обновляем username(varchar) -> user_id(integer) в measurement_batch
if exists(select from information_schema.columns where table_name='measurement_batch' and column_name='user_id') then
raise notice 'user_id column already exists in table measurement_batch';
else
alter table measurement_batch rename column username to user_id;
alter table measurement_batch alter column user_id type integer using null;
end if;

-- Привязываем measurement_batch(user_id) -> users(id)
if exists(select from information_schema.referential_constraints where constraint_name = 'measurement_batch_user_id_fkey') then
raise notice 'Foreign key measurement_batch(user_id) -> users(id) exists';
else
alter table measurement_batch add foreign key(user_id) references users(id);
end if;

-- Заполняем тестовые данные:
-- -- Пользователь
if exists(select from users) then
raise notice 'Test user exists';
else
insert into users(name, rank_id) values ('Левитан Всеволод Романович', (select id from ranks order by id desc limit 1));
end if;
-- -- Измерение
if exists(select from measurement_batch) then
raise notice 'Test batch exists';
else
insert into measurement_batch(start_period, user_id, pos_x, pos_y) values (now(), (select id from users limit 1), 69.00, 42.00);
end if;

END$$;

-- Проверяем
select batch.id, batch.start_period, concat(rnk.name, ' ', usr.name), batch.pos_x, batch.pos_y from measurement_batch batch join users usr on batch.user_id = usr.id join ranks rnk on usr.rank_id = rnk.id;