PostgresStudy2025/homework/3.class.sql

create table if not exists numeric_constants(key varchar primary key, value numeric);

-- Функция формата даты
create or replace function approx_report_date(t_date timestamptz)
	returns varchar(5)
	language plpgsql
	as
$$
begin
	return lpad(extract(day from t_date)::text, 2, '0') || lpad(extract(hour from t_date)::text, 2, '0') || floor((extract(minute from t_date) / 10))::varchar;
end;
$$;

-- Функция формата высоты
create or replace function approx_report_height(height numeric)
	returns varchar(4)
	language plpgsql
	as
$$
begin
	return lpad(height::text, 4, '0');
end;
$$;

-- Функция формата отклонения по давлению и температуре
create or replace function approx_report_deviation(pressure numeric, temperature numeric)
	returns varchar(5)
	language plpgsql
	as
$$
declare 
	pressure_dev numeric;
	temperature_dev numeric;
	res varchar(5);
begin
	pressure_dev = (pressure - (select value from numeric_constants where key = 'rel_pressure'));
	temperature_dev = (temperature - (select value from numeric_constants where key = 'rel_temperature'));
	temperature_dev := round(temperature_dev + calc_adjustment(calc_interpolation(temperature)));
	
	res = case when pressure_dev >= 0 then pressure_dev::text else ('5' || lpad((pressure_dev*-1)::text, 2, '0')) end;
	res = concat(res, lpad(temperature_dev::text, 2, '0'));
	
	return res;
end;
$$;

-- Тип приблизительного отчета
CREATE TYPE approx_report AS
(
	date character varying(5),
	height character varying(4),
	deviation character varying(5)
);

-- Функция получения приблизительного отчета
create or replace function approx_report(date timestamptz, height numeric, pressure numeric, temperature numeric)
	returns approx_report
	language plpgsql
	as
$$
begin
	return (approx_report_date(date)::varchar(5), approx_report_height(height)::varchar(4), approx_report_deviation(pressure, temperature)::varchar(5)) as approx_report;
end;
$$;

insert into numeric_constants(key, value) values ('rel_pressure', 750);
insert into numeric_constants(key, value) values ('rel_temperature', 15.9);