ЗАДАНИЕ 1.3. Приемы моделирования Справочников / parameter_functions

create or replace function ins_param(NAME varchar(50), SHORT_NAME varchar(10), TYPE BOOL, EI_CODE VARCHAR(3),
ENUM_ID INTEGER)
returns varchar(30)
/*функция: Функция добавления нового параметра в таблицу параметров
вход:name - название параметра, type - является ли параметр перечислением, ei_code - код ЕИ
выход: Сообщение о результате операции
эффекты:*/
as
$$

begin
insert into PARAMETER(NAME, SHORT_NAME, ENUM_TYPE, EI_CODE, ENUM_ID)
values (NAME, SHORT_NAME, TYPE, EI_CODE, ENUM_ID);
return 'Done!';
end;
$$
LANGUAGE plpgsql;


create or replace function ins_class_param(ID_CLASS INTEGER,
ID_PARAMETER INTEGER,
MIN_VALUE REAL,
MAX_VALUE REAL)
returns varchar(30)
/*функция: Функция добавления параметра класса
вход:MIN_VALUE - мин. возможное значение численного параметра, MAX_VALUE - макс.
выход: Сообщение о результате операции
эффекты:*/
as
$$
begin
insert into CLASS_PARAMETERS(ID_CLASS,
ID_PARAMETER,
MIN_VALUE,
MAX_VALUE)
values (ID_CLASS, ID_PARAMETER, MIN_VALUE, MAX_VALUE);
return 'Done!';
end;
$$
LANGUAGE plpgsql;


create or replace function check_param_value(PRODUCT_ID INTEGER,
PARAM_ID INTEGER,
VALUE REAL)
returns INTEGER
/*функция: Функция проверки добавляемого параметра
вход:
выход: возвращает 0, если проверка успешна, 1 - если параметра или класса не существует,
2 - если значение нарушает ограничения
эффекты:*/
as
$$
DECLARE
MIN_VAUE REAL;
MAX_VAUE REAL;
CLASS_ID INTEGER;
TYPE BOOL;
begin
SELECT ENUM_TYPE FROM parameter WHERE ID_PAR = PARAM_ID INTO TYPE;
IF TYPE = TRUE THEN
RETURN 0;
end if;

SELECT ID_CLASS FROM product WHERE ID_PROD = PRODUCT_ID INTO CLASS_ID;

if CLASS_ID NOT IN (SELECT ID_CLASS FROM CLASS_PARAMETERS WHERE ID_CLASS = CLASS_ID AND ID_PARAMETER = PARAM_ID) THEN
RETURN 1;
end if;

SELECT MIN_VALUE FROM CLASS_PARAMETERS WHERE ID_CLASS = CLASS_ID AND ID_PARAMETER = PARAM_ID INTO MIN_VAUE;
SELECT MAX_VALUE FROM CLASS_PARAMETERS WHERE ID_CLASS = CLASS_ID AND ID_PARAMETER = PARAM_ID INTO MAX_VAUE;

IF VALUE >= MIN_VAUE AND VALUE <= MAX_VAUE THEN
RETURN 0;
ELSE
RETURN 2;
end if;
end;
$$
LANGUAGE plpgsql;


create or replace function ins_product_param(PRODUCT_ID INTEGER,
PARAM_ID INTEGER,
VALUE REAL, ENUM_VALUE VARCHAR(30))
returns varchar(30)
/*функция: Функция добавления значения для параметра изделия
вход:
выход: Сообщение о результате операции
эффекты:*/
as
$$
DECLARE
CHECK_RES INTEGER;
begin
SELECT * FROM check_param_value(PRODUCT_ID, PARAM_ID, VALUE) INTO CHECK_RES;

IF CHECK_RES = 2 THEN
return 'Value is not correct!';
END IF;

IF CHECK_RES = 1 THEN
return 'This product or parameter does not exist in PRODUCT_CLASS!';
END IF;

insert into PRODUCT_PARAM(PRODUCT_ID, PARAM_ID, VALUE, ENUM_VALUE)
values (PRODUCT_ID, PARAM_ID, VALUE, ENUM_VALUE);

return 'Done!';
end;
$$
LANGUAGE plpgsql;


create or replace function change_product_param(PROD_ID INTEGER,
PAR_ID INTEGER,
NEW_VALUE REAL)
returns varchar(30)
/*функция: Функция изменения значения параметра изделия
вход:
выход: Сообщение о результате операции
эффекты:*/
as
$$
DECLARE
CHECK_RES INTEGER;
begin
SELECT * FROM check_param_value(PROD_ID, PAR_ID, NEW_VALUE) INTO CHECK_RES;

IF CHECK_RES = 2 THEN
return 'Value is not correct!';
END IF;

IF CHECK_RES = 1 THEN
return 'This product or parameter does not exist in PRODUCT_CLASS!';
END IF;

UPDATE PRODUCT_PARAM
SET VALUE = NEW_VALUE
WHERE PRODUCT_ID = PROD_ID AND PARAM_ID = PAR_ID;

return 'Done!';
end;
$$
LANGUAGE plpgsql;


DROP FUNCTION get_product_param(INTEGER);
create or replace function get_product_param(PROD_ID INTEGER)
returns TABLE
(
ID_PROD integer,
SHORT_NAME VARCHAR(10),
NAME VARCHAR(50),
VALUE REAL,
EI VARCHAR(30)
)
/*функция: Функция вывода всех параметров продукции
вход:
выход: Таблица всех параметров указанного продукта
эффекты:*/
as
$$
begin
RETURN QUERY
SELECT PRODUCT_ID, P.SHORT_NAME, P.NAME, product_param.VALUE, E.NAME
FROM product_param
INNER JOIN parameter p on p.id_par = product_param.param_id
INNER JOIN ei e on E.CODE = p.ei_code
WHERE PRODUCT_ID = PROD_ID;
end;
$$
LANGUAGE plpgsql;


create or replace function get_product_on_param(PAR_ID INTEGER, PAR_VALUE REAL)
returns TABLE
(
PROD_NAME varchar(50),
ID_PROD integer,
SHORT_NAME VARCHAR(10),
NAME VARCHAR(50),
VALUE REAL,
EI VARCHAR(30)
)
/*функция: Получаем список продукции по значению указанного параметра
вход:
выход:
эффекты:*/
as
$$
begin
RETURN QUERY
SELECT PR.NAME, product_id, P.SHORT_NAME, P.NAME, product_param.VALUE, E.NAME
FROM product_param
INNER JOIN parameter p on p.id_par = product_param.param_id
INNER JOIN ei e on E.CODE = p.ei_code
INNER JOIN product PR ON PRODUCT_ID = PR.id_prod
WHERE PAR_ID = product_param.param_id AND PAR_VALUE = product_param.VALUE;
end;
$$
LANGUAGE plpgsql;


create or replace function get_class_param(CLASS_ID INTEGER)
returns TABLE
(
ID_CLASS integer,
CLASS_NAME varchar(50),
SHORT_NAME VARCHAR(10),
NAME VARCHAR(50),
EI VARCHAR(30)
)
/*функция: Функция вывода всех параметров класса изделий
вход:
выход:
эффекты:*/
as
$$
begin
RETURN QUERY
SELECT CS.ID_CLASS, PR.NAME, P.SHORT_NAME, P.NAME, E.NAME
FROM CLASS_PARAMETERS AS CS
INNER JOIN parameter p on p.id_par = CS.ID_PARAMETER
INNER JOIN ei e on E.CODE = p.ei_code
INNER JOIN PRODUCT_CLASS PR ON CLASS_ID = PR.ID_CLASS
WHERE CS.id_class = CLASS_ID;
end;
$$
LANGUAGE plpgsql;