PostgresStudy2025/homework/2.sql

do $$
begin
create table if not exists temperature_adjustments
(
	id serial primary key,
	temperature numeric,
	adjustment numeric
);
if exists(select from temperature_adjustments limit 1) then
raise notice 'Temperature_adjustments';
else
insert into temperature_adjustments(temperature, adjustment)
values
(0, 0), (5, 0.5), (10, 1), (15, 1), (20, 1.5), (25, 2), (30, 3.5), (40, 4.5);
end if;

if not exists(select from pg_type where typname = 'interpolation') then
create type interpolation as (temperature_b numeric, temperature_t numeric, adjustment_b numeric, adjustment_t numeric, temperature numeric);
end if;

create or replace function calc_interpolation(temp1 numeric)
	returns interpolation
	language plpgsql as $func$
	declare interp interpolation;
begin
	select temperature_b, temperature_t, adjustment_b, adjustment_t, temp1 as interpolation into interp from
		(select temperature as temperature_b, adjustment as adjustment_b from temperature_adjustments where temperature <= temp1 order by temperature desc limit 1)
	full join
		(select temperature as temperature_t, adjustment as adjustment_t from temperature_adjustments where temperature >= temp1 order by temperature limit 1)
	on true limit 1;

	if interp.temperature_b is null or interp.temperature_t is null then raise exception ':c'; end if;
	
	return interp;
end;
$func$;

end $$;

do $$
begin
create or replace function calc_adjustment(interp interpolation)
	returns numeric
	language plpgsql as $func$
	declare
		adjustment numeric;
		temp_min numeric;
		temp_max numeric;
begin
	select min(temperature) from temperature_adjustments into temp_min;
	select max(temperature) from temperature_adjustments into temp_max;
	
	if interp.temperature <= temp_min or interp.temperature_b = interp.temperature or interp.temperature_t is null then
		select interp.adjustment_b into adjustment;
	elseif interp.temperature >= temp_max or interp.temperature_t = interp.temperature or interp.temperature_b is null then
		select interp.adjustment_t into adjustment;
	elseif interp.temperature_b = interp.temperature_t then
		select 0 into adjustment;
	else 
		select interp.adjustment_b+((interp.temperature - interp.temperature_b) / (interp.temperature_t - interp.temperature_b)) * (interp.adjustment_t - interp.adjustment_b)
		into adjustment;
	end if;
	return adjustment;
end;
$func$;

end $$;