ЗАДАНИЕ 1.1. Приемы моделирования Классификаторов / main_procedures

/*Добавление новой ЕИ в таблицу*/
create or replace function ins_ei(CODE integer, SHORT_NAME varchar(10), NAME varchar(50))
returns INTEGER
as
'
insert into EI(SHORT_NAME, NAME, CODE)
values (CODE, SHORT_NAME, NAME);
select 1
'
LANGUAGE sql;

/*Операция добавления нового класса в классификатор изделий*/
CREATE OR REPLACE FUNCTION ins_class(main_class INTEGER, short_name VARCHAR(10), name VARCHAR(50), base_ei INTEGER)
returns INTEGER
as
'
begin
if (exists(
select BASE_EI
from EI
where EI.EI_ID = base_ei)
) then
insert into PRODUCT_CLASS(MAIN_CLASS, SHORT_NAME, NAME, BASE_EI)
values (main_class, short_name, name, base_ei);
return 1;
end if;

insert into PRODUCT_CLASS(MAIN_CLASS, SHORT_NAME, NAME, BASE_EI)
values (NULL, short_name, name, NULL);
return 1;
end;
'
LANGUAGE plpgsql;


/*Поиск всех подклассов класса*/
CREATE OR REPLACE FUNCTION find_gr_gr(class INTEGER)
returns TABLE(ID integer, MAIN INTEGER, NAME VARCHAR(50), BASE INTEGER)
as
$$
begin
return QUERY
SELECT PRODUCT_CLASS.ID_CLASS, PRODUCT_CLASS.MAIN_CLASS,PRODUCT_CLASS.NAME, PRODUCT_CLASS.BASE_EI
FROM PRODUCT_CLASS
WHERE ID_CLASS = class
union
(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 INTEGER)
as
$$
begin
return QUERY
SELECT PRODUCT_CLASS.ID_CLASS, PRODUCT_CLASS.MAIN_CLASS, PRODUCT_CLASS.NAME, PRODUCT_CLASS.BASE_EI
FROM PRODUCT_CLASS
WHERE ID_CLASS = class
union
(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, SHORT_NAME varchar(10), NAME varchar(50))
returns INTEGER
as
'
begin
if (exists(
select ID_CLASS
from PRODUCT_CLASS
where PRODUCT_CLASS.ID_CLASS = ID_CL)
) then
insert into PROD(ID_CL, short_name, NAME)
values (ID_CL, SHORT_NAME, NAME);
return 1;
end if;
insert into PROD(ID_CL, short_name, NAME)
values (NULL, SHORT_NAME, NAME);
return 1;
end;
'
LANGUAGE plpgsql;

/*Вывести список всех продукции указанного класса*/
create or replace function FIND_LIST(class integer)
returns TABLE(ID integer, prod_name VARCHAR(50), shotr_NAME VARCHAR(10), ei_name varchar(10), class_id INTEGER, class_name varchar(50))
as
$$
begin
return QUERY
with t as (select * from find_gr_gr(class))
select ID_PROD, PROD.NAME, PROD.SHORT_NAME, ei.name, prod.id_cl, PRODUCT_CLASS.name from prod
inner join PRODUCT_CLASS on PRODUCT_CLASS.id_class = prod.id_cl
inner join EI on EI.ei_id = PRODUCT_CLASS.base_ei
where prod.id_cl = any(select t.id from t);
end;
$$
LANGUAGE plpgsql;