PostgresStudy2025/homework/9.sql

do $$
begin 
create sequence if not exists calc_header_adjustment_seq;
create table if not exists calc_header_adjustment
(
	id integer not null primary key default nextval('public.calc_header_adjustment_seq'),
	measurment_type_id integer not null,
	header varchar(100) not null,
	description text not null,
	values integer[] not null
);
create unique index ix_calc_header_adjustment_header_type on calc_header_adjustment(measurment_type_id, header);
insert into calc_header_adjustment(measurment_type_id, header, description, values) 
values (1, 'table2', 'Заголовок для Таблицы № 2 (ДМК)', array[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 40, 50]),
       (2, 'table2','Заголовок для Таблицы № 2 (ВР)', array[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 40, 50]),
	   (2, 'table3', 'Заголовок для Таблицы № 3 (ВР)', array[40,50,60,70,80,90,100,110,120,130,140,150]);
create sequence if not exists calc_height_adjustment_seq;
create table if not exists calc_height_adjustment
(
	id integer primary key not null default nextval('public.calc_height_adjustment_seq'),
	height integer not null,
	measurment_type_id integer not null
);
insert into calc_height_adjustment(height, measurment_type_id)
values(200,1),(400,1),(800,1),(1200,1),(1600,1),(2000,1),(2400,1),(3000,1),(4000,1),
	  (200,2),(400,2),(800,2),(1200,2),(1600,2),(2000,2),(2400,2),(3000,2),(4000,2);	  
create sequence if not exists calc_temperature_height_adjustment_seq;
create table if not exists calc_temperature_height_adjustment
(
	id integer primary key not null default nextval('public.calc_temperature_height_adjustment_seq'),
	calc_height_id integer not null,
	calc_temperature_header_id integer not null,
	positive_values numeric[],
	negative_values numeric[]
);
insert into calc_temperature_height_adjustment(calc_height_id, calc_temperature_header_id, positive_values, negative_values)
values
(10,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[ -1, -2, -3, -4, -5, -6, -7, -8, -8, -9, -20, -29, -39, -49]), 
(11,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -3, -4, -5, -6, -6, -7, -8, -9, -19, -29, -38, -48]), 
(12,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -3, -4, -5, -6, -6, -7, -7, -8, -18, -28, -37, -46]), 
(13,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -3, -4, -4, -5, -5, -6, -7, -8, -17, -26, -35, -44]), 
(14,1,array[ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -3, -3, -4, -4, -5, -6, -7, -7, -17, -25, -34, -42]), 
(15,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -3, -3, -4, -4, -5, -6, -6, -7, -16, -24, -32, -40]), 
(16,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -2, -3, -4, -4, -5, -5, -6, -7, -15, -23, -31, -38]), 
(17,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -2, -3, -4, -4, -4, -5, -5, -6, -15, -22, -30, -37]), 
(18,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[ -1, -2, -2, -3, -4, -4, -4, -4, -5, -6, -14, -20, -27, -34]); 
insert into calc_temperature_height_adjustment(calc_height_id, calc_temperature_header_id, positive_values, negative_values)
values
(1,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[ -1, -2, -3, -4, -5, -6, -7, -8, -8, -9, -20, -29, -39, -49]), 
(2,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -3, -4, -5, -6, -6, -7, -8, -9, -19, -29, -38, -48]), 
(3,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -3, -4, -5, -6, -6, -7, -7, -8, -18, -28, -37, -46]), 
(4,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -3, -4, -4, -5, -5, -6, -7, -8, -17, -26, -35, -44]), 
(5,1,array[ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -3, -3, -4, -4, -5, -6, -7, -7, -17, -25, -34, -42]), 
(6,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -3, -3, -4, -4, -5, -6, -6, -7, -16, -24, -32, -40]), 
(7,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -2, -3, -4, -4, -5, -5, -6, -7, -15, -23, -31, -38]), 
(8,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[-1, -2, -2, -3, -4, -4, -4, -5, -5, -6, -15, -22, -30, -37]), 
(9,1,array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, 30, 30], array[ -1, -2, -2, -3, -4, -4, -4, -4, -5, -6, -14, -20, -27, -34]); 
create sequence calc_wind_speed_height_adjustment_seq;
drop table if exists calc_wind_speed_height_adjustment;
create table calc_wind_speed_height_adjustment
(
	id integer not null primary key default nextval('public.calc_wind_speed_height_adjustment_seq'),
	calc_height_id integer not null,
	values integer[] not null,
	delta integer not null
);
truncate table calc_wind_speed_height_adjustment;
insert into calc_wind_speed_height_adjustment(calc_height_id, values, delta)
values
(1, array[3, 4, 5, 6, 7, 7, 8, 9, 10, 11, 12, 12], 0),
(2, array[4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15], 1),
(3, array[4, 5, 6, 7, 8, 9, 10, 11, 13, 14, 15, 16], 2),
(4, array[4, 5, 7, 8, 8, 9, 11, 12, 13, 15, 15, 16], 2),
(5, array[4, 6, 7, 8, 9, 10, 11, 13, 14, 15, 17, 17], 3),
(6, array[4, 6, 7, 8, 9, 10, 11, 13, 14, 16, 17, 18], 3),
(7, array[4, 6, 8, 9, 9, 10, 12, 14, 15, 16, 18, 19], 3),
(8, array[5, 6, 8, 9, 10, 11, 12, 14, 15, 17, 18, 19], 4),
(9, array[5, 6, 8, 9, 10, 11, 12, 14, 16, 18, 19, 20], 4);
drop type if exists temperature_adjustment cascade;
create type temperature_adjustment as
(
	calc_height_id integer,
	height integer,
	temperature_deviation integer
);
drop type if exists wind_direction_adjustment cascade;
create type wind_direction_adjustment as
(
	calc_height_id integer,
	height integer,
 
	wind_speed_deviation integer,
 
	wind_deviation integer
);
end$$;
do $$
begin
create or replace procedure public.calculate_temperature_deviation(
    in pmt_measurement_type_id integer,
    in pmt_temperature numeric(8,2),
    inout pmt_adjustments public.temperature_adjustment[]
)
language 'plpgsql'
as $body$
declare
    var_row record;
    var_index integer;
    var_header_adj integer[];
    var_right_index integer;
    var_left_index integer;
    var_header_index integer;
    var_dev integer;
    var_table integer[];
    var_adj public.temperature_adjustment;
    var_table_row text;
begin
    
    if not exists (
        select 1
        from public.calc_height_adjustment as t1
        inner join public.calc_temperature_height_adjustment as t2 
        on t2.calc_height_id = t1.id
        where t1.measurment_type_id = pmt_measurement_type_id
    ) then
        raise exception 'недостаточно данных';
    end if;    raise notice '| высота   | поправка  |';
    raise notice '|----------|-----------|';    for var_row in
        select t2.*, t1.height 
        from public.calc_height_adjustment as t1
        inner join public.calc_temperature_height_adjustment as t2 
        on t2.calc_height_id = t1.id
        where t1.measurment_type_id = pmt_measurement_type_id
    loop
        
        var_index := pmt_temperature::integer;
        
        var_header_adj := (select values from public.calc_header_adjustment
            where id = var_row.calc_temperature_header_id and header = 'table2');        
        if array_length(var_header_adj, 1) = 0 then
            raise exception 'высота % - некорректные параметры', var_row.height;
        end if;        if array_length(var_header_adj, 1) < var_index then
            raise exception 'высота % - некорректные параметры', var_row.height;
        end if;        
        var_right_index := abs(var_index % 10);
        var_header_index := abs(var_index) - var_right_index;        
        if pmt_temperature >= 0 then
            var_table := var_row.positive_values;
        else
            var_table := var_row.negative_values;
        end if;        if var_header_index = 0 then
            var_header_index := 1;
        end if;        var_left_index := var_header_adj[var_header_index];
        if var_left_index = 0 then
            var_left_index := 1;
        end if;        
        var_dev := var_table[var_left_index] + var_table[var_right_index];        select '|' || lpad(var_row.height::text, 10, ' ') || '|' || lpad(var_dev::text, 11, ' ') || '|'
        into var_table_row;        raise notice '%', var_table_row;        var_adj.calc_height_id := var_row.calc_height_id;
        var_adj.height := var_row.height;
        var_adj.temperature_deviation := var_dev;
        pmt_adjustments := array_append(pmt_adjustments, var_adj);
    end loop;    raise notice '|----------|-----------|';
end;
$body$;
create or replace procedure public.calculate_wind_deviation(
    in pmt_bullet_speed numeric,
    in pmt_wind_direction numeric,
    in pmt_measurement_type_id integer,
    inout pmt_adjustments public.wind_direction_adjustment[]
)
language 'plpgsql'
as $body$
declare
    var_row record;
    var_adj public.wind_direction_adjustment;
    var_header_adj integer[];
    var_index integer;
    var_header_index integer;
    var_table integer[];
    var_dev integer;
    var_table_row text;
begin
    if coalesce(pmt_bullet_speed, -1) < 0 and coalesce(pmt_wind_direction, -1) < 0 then
        raise exception 'некорректные параметры. pmt_bullet_speed=% или pmt_wind_direction=%', pmt_bullet_speed, pmt_wind_direction;
    end if;

    if not exists (
        select 1 from public.calc_height_adjustment 
        where measurment_type_id = pmt_measurement_type_id
    ) then
        raise exception '% отсутствуют значения высот', pmt_measurement_type_id;
    end if;

    raise notice '| высота   | скорость  | направление |';
    raise notice '|----------|-----------|-------------|';

    for var_row in
        select t1.height, t2.* 
        from calc_height_adjustment as t1
        left join public.calc_wind_speed_height_adjustment as t2
        on t2.calc_height_id = t1.id
        where t1.measurment_type_id = pmt_measurement_type_id
    loop
        var_adj.calc_height_id := var_row.calc_height_id;
        var_adj.height := var_row.height;
        var_adj.wind_speed_deviation := 0;
        var_adj.wind_deviation := 0;

        if pmt_bullet_speed is not null then
            var_index := (pmt_bullet_speed / 10)::integer - 4;
            if var_index < 0 then
                var_index := 1;
            end if;

            var_header_adj := (select values from public.calc_header_adjustment
                where header = 'table3'
                and measurment_type_id = pmt_measurement_type_id);

            if array_length(var_header_adj, 1) = 0 then
                raise exception 'некорректные данные для скорости ветра';
            end if;

            if array_length(var_header_adj, 1) < var_index then
                raise exception 'некорректные данные для скорости ветра';
            end if;

            var_header_index := abs(var_index % 10);
            var_table := var_row.values;
            var_dev := var_table[var_header_index];

            var_adj.wind_speed_deviation := var_dev;
            var_adj.wind_deviation := var_row.delta;
        end if;

        if pmt_wind_direction is not null then
            var_adj.wind_deviation := var_adj.wind_deviation + (pmt_wind_direction / 10)::integer; -- Примерная логика
        end if;

        select '|' || lpad(var_row.height::text, 10, ' ') || '|' || 
               lpad(var_adj.wind_speed_deviation::text, 11, ' ') || '|' || 
               lpad(var_adj.wind_deviation::text, 13, ' ') || '|'
        into var_table_row;

        raise notice '%', var_table_row;

        pmt_adjustments := array_append(pmt_adjustments, var_adj);
    end loop;

    raise notice '|----------|-----------|-------------|';
end;
$body$;
end$$;
do $$
declare
    var_temperature_adjustments public.temperature_adjustment[];
    var_wind_speed_adjustments public.wind_direction_adjustment[];
    var_wind_direction_adjustments public.wind_direction_adjustment[];
begin
    
    raise notice 'расчет отклонений температуры для ветрового ружья';
    call public.calculate_temperature_deviation(
        pmt_measurement_type_id => 2, 
        pmt_temperature => 3.0, 
        pmt_adjustments => var_temperature_adjustments
    );
    raise notice 'отклонение температуры: %', var_temperature_adjustments;    
    raise notice 'расчет средней скорости ветра для ветрового ружья';
    call public.calculate_wind_speed_deviation(
        pmt_bullet_speed => 14.0, 
        pmt_measurement_type_id => 2, 
        pmt_adjustments => var_wind_speed_adjustments
    );
    raise notice 'скорость ветра: %', var_wind_speed_adjustments;    
    raise notice 'расчет среднего направления ветра для ветрового ружья';
    call public.calculate_wind_direction_deviation(
        pmt_wind_direction => 45.0, 
        pmt_measurement_type_id => 2, 
        pmt_adjustments => var_wind_direction_adjustments
    );
    raise notice 'среднее направление ветра: %', var_wind_direction_adjustments;
end $$;
do $$
begin
create or replace procedure public.calculate_temperature_deviation_dmk(
    in pmt_measurement_type_id integer,
    in pmt_temperature numeric(8,2),
    inout pmt_adjustments public.temperature_adjustment[]
)
language 'plpgsql'
as $body$
declare
    var_row record;
    var_index integer;
    var_header_adj integer[];
    var_right_index integer;
    var_left_index integer;
    var_header_index integer;
    var_dev integer;
    var_table integer[];
    var_adj public.temperature_adjustment;
    var_table_row text;
begin
    
    if not exists (
        select 1
        from public.calc_height_adjustment as t1
        inner join public.calc_temperature_height_adjustment as t2 
        on t2.calc_height_id = t1.id
        where t1.measurment_type_id = pmt_measurement_type_id
    ) then
        raise exception 'недостаточно данных';
    end if;    raise notice '| высота   | поправка  |';
    raise notice '|----------|-----------|';    for var_row in
        select t2.*, t1.height 
        from public.calc_height_adjustment as t1
        inner join public.calc_temperature_height_adjustment as t2 
        on t2.calc_height_id = t1.id
        where t1.measurment_type_id = pmt_measurement_type_id
    loop
        
        var_index := pmt_temperature::integer;
        
        var_header_adj := (select values from public.calc_header_adjustment
            where id = var_row.calc_temperature_header_id and header = 'table2');        
        if array_length(var_header_adj, 1) = 0 then
            raise exception 'высота % - некорректные данные', var_row.height;
        end if;        if array_length(var_header_adj, 1) < var_index then
            raise exception 'высота % - некорректные данные', var_row.height;
        end if;        
        var_right_index := abs(var_index % 10);
        var_header_index := abs(var_index) - var_right_index;        
        if pmt_temperature >= 0 then
            var_table := var_row.positive_values;
        else
            var_table := var_row.negative_values;
        end if;        if var_header_index = 0 then
            var_header_index := 1;
        end if;        var_left_index := var_header_adj[var_header_index];
        if var_left_index = 0 then
            var_left_index := 1;
        end if;        
        var_dev := var_table[var_left_index] + var_table[var_right_index];        select '|' || lpad(var_row.height::text, 10, ' ') || '|' || lpad(var_dev::text, 11, ' ') || '|'
        into var_table_row;        raise notice '%', var_table_row;        var_adj.calc_height_id := var_row.calc_height_id;
        var_adj.height := var_row.height;
        var_adj.temperature_deviation := var_dev;
        pmt_adjustments := array_append(pmt_adjustments, var_adj);
    end loop;    raise notice '|----------|-----------|';
end;
$body$;
create or replace procedure public.calculate_wind_speed_deviation_dmk(
    in pmt_ground_wind_speed numeric,
    in pmt_measurement_type_id integer,
    inout pmt_adjustments public.wind_direction_adjustment[]
)
language 'plpgsql'
as $body$
declare
    var_row record;
    var_index integer;
    var_adj public.wind_direction_adjustment;
    var_header_adj integer[];
    var_header_index integer;
    var_table integer[];
    var_dev integer;
    var_table_row text;
begin
    if coalesce(pmt_ground_wind_speed, -1) < 0 then
        raise exception 'некорректные данные. pmt_ground_wind_speed=%', pmt_ground_wind_speed;
    end if;    if not exists (
        select 1 from public.calc_height_adjustment 
        where measurment_type_id = pmt_measurement_type_id
    ) then
        raise exception '% - не найдены значения высот в таблице', pmt_measurement_type_id;
    end if;    
    var_index := (pmt_ground_wind_speed / 10)::integer - 4;
    if var_index < 0 then
        var_index := 1;
    end if;    
    var_header_adj := (select values from public.calc_header_adjustment
        where header = 'table3'
        and measurment_type_id = pmt_measurement_type_id);    
    if array_length(var_header_adj, 1) = 0 then
        raise exception 'некорректные данные';
    end if;    if array_length(var_header_adj, 1) < var_index then
        raise exception 'некорректные данные';
    end if;    raise notice '| высота   | поправка  |';
    raise notice '|----------|-----------|';    for var_row in
        select t1.height, t2.* 
        from calc_height_adjustment as t1
        inner join public.calc_wind_speed_height_adjustment as t2
        on t2.calc_height_id = t1.id
        where t1.measurment_type_id = pmt_measurement_type_id
    loop
        
        var_header_index := abs(var_index % 10);
        var_table := var_row.values;        
        var_dev := var_table[var_header_index];        select '|' || lpad(var_row.height::text, 10, ' ') || '|' || lpad(var_dev::text, 11, ' ') || '|'
        into var_table_row;        raise notice '%', var_table_row;        var_adj.calc_height_id := var_row.calc_height_id;
        var_adj.height := var_row.height;
        var_adj.wind_speed_deviation := var_dev;
        var_adj.wind_deviation := var_row.delta;
        pmt_adjustments := array_append(pmt_adjustments, var_adj);
    end loop;    raise notice '|----------|-----------|';
end;
$body$;
create or replace procedure public.calculate_wind_direction_deviation_dmk(
    in pmt_wind_direction numeric,
    in pmt_measurement_type_id integer,
    inout pmt_adjustments public.wind_direction_adjustment[]
)
language 'plpgsql'
as $body$
declare
    var_row record;
    var_adj public.wind_direction_adjustment;
begin
    for var_row in
        select t1.height, t2.* 
        from calc_height_adjustment as t1
        inner join public.calc_wind_speed_height_adjustment as t2
        on t2.calc_height_id = t1.id
        where t1.measurment_type_id = pmt_measurement_type_id
    loop
        var_adj.calc_height_id := var_row.calc_height_id;
        var_adj.height := var_row.height;
        var_adj.wind_speed_deviation := var_row.delta;
        var_adj.wind_deviation := pmt_wind_direction + var_row.delta;
        pmt_adjustments := array_append(pmt_adjustments, var_adj);
    end loop;
end;
$body$;
end$$;
do $$
declare
    var_temperature_adjustments public.temperature_adjustment[];
    var_wind_speed_adjustments public.wind_direction_adjustment[];
    var_wind_direction_adjustments public.wind_direction_adjustment[];
begin
    
    raise notice 'расчет отклонений температуры для дмк';
    call public.calculate_temperature_deviation_dmk(
        pmt_measurement_type_id => 1, 
        pmt_temperature => 3.0, 
        pmt_adjustments => var_temperature_adjustments
    );
    raise notice 'отклонение температуры: %', var_temperature_adjustments;    
    raise notice 'расчет средней скорости ветра для дмк';
    call public.calculate_wind_speed_deviation_dmk(
        pmt_ground_wind_speed => 14.0, 
        pmt_measurement_type_id => 1, 
        pmt_adjustments => var_wind_speed_adjustments
    );
    raise notice 'средняя скорость ветра: %', var_wind_speed_adjustments;    
    raise notice 'расчет среднего направления ветра для дмк';
    call public.calculate_wind_direction_deviation_dmk(
        pmt_wind_direction => 45.0, 
        pmt_measurement_type_id => 1, 
        pmt_adjustments => var_wind_direction_adjustments
    );
    raise notice 'среднее направление ветра: %', var_wind_direction_adjustments;
end $$;