PostgresStudy2025/homework/3.sql

CREATE TABLE IF NOT EXISTS measure_settings (
    id SERIAL PRIMARY KEY,
    parameter_name VARCHAR(50) NOT NULL UNIQUE,
    min_value NUMERIC NOT NULL,
    max_value NUMERIC NOT NULL,
    unit VARCHAR(20)
);

INSERT INTO measure_settings (parameter_name, min_value, max_value, unit)
VALUES 
('temperature', -58, 58, '°C'),
('pressure', 500, 900, 'mmHg'),
('wind_direction', 0, 59, 'degrees');

CREATE TYPE measurement_data AS (
    temperature NUMERIC,
    pressure NUMERIC,
    wind_direction NUMERIC
);

CREATE OR REPLACE FUNCTION validate_measurement(
    input_temperature NUMERIC,
    input_pressure NUMERIC,
    input_wind_direction NUMERIC
) RETURNS measurement_data AS $$
DECLARE
    validated_data measurement_data;
BEGIN
    IF input_temperature < (SELECT min_value FROM measure_settings WHERE parameter_name = 'temperature') OR
       input_temperature > (SELECT max_value FROM measure_settings WHERE parameter_name = 'temperature') THEN
        RAISE EXCEPTION 'Temperature out of range. Allowed range: % to % °C',
            (SELECT min_value FROM measure_settings WHERE parameter_name = 'temperature'),
            (SELECT max_value FROM measure_settings WHERE parameter_name = 'temperature');
    END IF;

    IF input_pressure < (SELECT min_value FROM measure_settings WHERE parameter_name = 'pressure') OR
       input_pressure > (SELECT max_value FROM measure_settings WHERE parameter_name = 'pressure') THEN
        RAISE EXCEPTION 'Pressure out of range. Allowed range: % to % mmHg',
            (SELECT min_value FROM measure_settings WHERE parameter_name = 'pressure'),
            (SELECT max_value FROM measure_settings WHERE parameter_name = 'pressure');
    END IF;

    IF input_wind_direction < (SELECT min_value FROM measure_settings WHERE parameter_name = 'wind_direction') OR
       input_wind_direction > (SELECT max_value FROM measure_settings WHERE parameter_name = 'wind_direction') THEN
        RAISE EXCEPTION 'Wind direction out of range. Allowed range: % to % degrees',
            (SELECT min_value FROM measure_settings WHERE parameter_name = 'wind_direction'),
            (SELECT max_value FROM measure_settings WHERE parameter_name = 'wind_direction');
    END IF;

    validated_data.temperature := input_temperature;
    validated_data.pressure := input_pressure;
    validated_data.wind_direction := input_wind_direction;

    RETURN validated_data;
END;
$$ LANGUAGE plpgsql;