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

create or replace function ins_ei(CODE varchar(3), SHORT_NAME varchar(10), NAME varchar(50))
returns varchar(30)
/*функция: Добавление новой ЕИ в таблицу
вход:CODE - код ЕИ, SHORT_NAME - обозначение ЕИ, NAME - название ЕИ
выход: Сообщение о результате операции
эффекты:*/
as
$$
begin
insert into EI(SHORT_NAME, NAME, CODE)
values (SHORT_NAME, NAME, CODE);
return 'Done!';
end;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION ins_product_class(main_class INTEGER, short_name VARCHAR(10), name VARCHAR(50), ei_code varchar(3))
returns varchar(30)
/*функция: Операция добавления нового класса в классификатор изделий
вход:main_class - код родительского класса, short_name - обозначение класса, NAME - класса
выход: Сообщение о результате операции
эффекты:*/
as
$$
begin
if ei_code is null then
insert into PRODUCT_CLASS(MAIN_CLASS, SHORT_NAME, NAME, BASE_EI)
values (NULL, short_name, name, NULL);
return 'Done!';
end if;

if ei_code in (
select ei_code
from EI
where EI.code = ei_code) then
insert into PRODUCT_CLASS(MAIN_CLASS, SHORT_NAME, NAME, BASE_EI)
values (main_class, short_name, name, ei_code);
return 'Done!';
else
return 'This unit does not exist!';
end if;


end;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION find_children(class INTEGER)
returns TABLE(ID integer, MAIN INTEGER, NAME VARCHAR(50), BASE VARCHAR(3))
/*функция: Поиск всех подклассов класса
вход:class - id класса
выход: Вывод таблицы всех потомков класса изделий
эффекты:*/
as
$$
begin
return QUERY
WITH RECURSIVE r AS (
SELECT PRODUCT_CLASS.ID_CLASS, PRODUCT_CLASS.MAIN_CLASS,PRODUCT_CLASS.NAME, PRODUCT_CLASS.BASE_EI
FROM PRODUCT_CLASS
WHERE MAIN_CLASS = class

UNION

SELECT PRODUCT_CLASS.ID_CLASS, PRODUCT_CLASS.MAIN_CLASS, PRODUCT_CLASS.NAME, PRODUCT_CLASS.BASE_EI
FROM PRODUCT_CLASS
JOIN r
ON PRODUCT_CLASS.MAIN_CLASS = r.ID_CLASS
)
SELECT * from r
order by id_class;
end;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION find_parent(class INTEGER)
returns TABLE(ID integer, MAIN INTEGER, NAME VARCHAR(50), BASE VARCHAR(3))
/*функция: Поиск всех родителей класса
вход:class - id класса, родителей которого ищем
выход: Вывод таблицы всех родителей класса изделий
эффекты:*/
as
$$
begin
return QUERY
WITH RECURSIVE r AS (
with t as (SELECT MAIN_CLASS AS parent FROM PRODUCT_CLASS
WHERE ID_CLASS = class)

SELECT PRODUCT_CLASS.ID_CLASS, PRODUCT_CLASS.MAIN_CLASS,PRODUCT_CLASS.NAME, PRODUCT_CLASS.BASE_EI
FROM PRODUCT_CLASS, t
WHERE ID_CLASS = t.parent

UNION

SELECT PRODUCT_CLASS.ID_CLASS, PRODUCT_CLASS.MAIN_CLASS, PRODUCT_CLASS.NAME, PRODUCT_CLASS.BASE_EI
FROM PRODUCT_CLASS
JOIN r
ON PRODUCT_CLASS.ID_CLASS = r.MAIN_CLASS
)
SELECT * from r
order by id_class DESC;
end;
$$
LANGUAGE plpgsql;



create or replace function ins_prod(ID_CL integer, SHRT_NAME varchar(10), PRODUCT_NAME varchar(50))
returns varchar(30)
/*функция: Добавление продукции в таблицу
вход:ID_CL - класс, к которому будет относиться изделие
выход: Сообщение о результате опреации
эффекты:*/
as
$$
begin
if ID_CL IS NOT NULL THEN
insert into PRODUCT(ID_CLASS, short_name, NAME)
values (ID_CL, SHRT_NAME, PRODUCT_NAME);
return 'Done!';
ELSE
return 'This class does not exist!';
END IF;
end;
$$
LANGUAGE plpgsql;



create or replace function find_products(class integer)
/*функция: Вывести список всех продукции указанного класса
вход:ID_CL - класс, список продукции которого хотим получить
выход: Таблица ос списком продукции заданного класса
эффекты:*/
returns TABLE(ID integer, prod_name VARCHAR(50), short_name VARCHAR(10), ei_name varchar(10), class_id INTEGER, class_name varchar(50))
as
$$
begin
return QUERY
with t as (select * from find_children(class))
select ID_PROD, PRODUCT.NAME, PRODUCT.SHORT_NAME, ei.name, PRODUCT.ID_CLASS, PRODUCT_CLASS.name from PRODUCT
inner join PRODUCT_CLASS on PRODUCT_CLASS.id_class = PRODUCT.ID_CLASS
inner join EI on EI.CODE = PRODUCT_CLASS.base_ei
where PRODUCT.ID_CLASS = any(select t.id from t);
end;
$$
LANGUAGE plpgsql;