PostgresStudy2025/homework/1.sql

begin;
DO $$ 
begin

-- Создаем таблицу званий
create table if not exists ranks (id serial primary key, name varchar(32));

-- Создаем таблицу пользователей
create table if not exists users (id serial primary key, name varchar(64), rank_id integer references ranks(id));

-- Заполняем звания
if exists(select from ranks limit 1) 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;
	delete from measurement_params;
	delete from measurement_batch;
	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 limit 1) 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;

END $$;

commit;

begin;

DO $$
begin

-- -- Измерение
if exists(select id from measurement_batch limit 1) 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$$;

commit;

begin;

do $$
begin

-- Данные измерения
if exists(select id from measurement_params limit 1) then 
	raise notice 'Test params exists';
else
	insert into measurement_params(measurement_type_id, measurement_batch_id, height, temperature, pressure, wind_speed, wind_direction, bullet_speed) values ((select id from measurement_types limit 1), (select id from measurement_batch limit 1), 100, 15, 750, 0, 0, 0);
end if;

end $$;

commit;