МиСПрИС_Задание3_Петрова_Романова_Заболотников_9373
.docx*/
as $$
declare flagName int default 0;
declare flagUom int default 0;
declare flagType int default 0;
declare flagEnum int default 0;
begin
select id_parameter from parameters where namePar=name_parameter into flagName;
select id_uom from unit_of_measure where id_uom = uomPar into flagUom;
select id_type from parameter_type where id_type = idType into flagType;
select id_enum from enumeration where id_enum = idEnum into flagEnum;
if flagName is null then
if flagUom is null and uomPar is not null then
raise notice 'This id_uom does not exist. Db was not updated.';
return 0;
else
if flagType is null and idType is not null then
raise notice 'This id_type does not exist. Db was not updated.';
return 0;
else
if flagEnum is null and idEnum is not null then
raise notice 'This id_enum does not exist. Db was not updated.';
return 0;
else
insert into parameters(name_parameter,id_type, uom_parameter,id_enum) values (namePar,idType,uomPar,idEnum);
return 1;
end if;
end if;
end if;
else
raise notice 'This parameter already exists! Db was not updated.';
return 0;
end if;
end $$ LANGUAGE plpgsql;
-- Удаление параметра
CREATE OR REPLACE function delete_parameter (idPar int) RETURNS int
/*функция: удаление параметра
вход: idPar - id параметра
выход: 1 - БД обновлена, 0 - Ошибка
эффекты: 1. Если нет такого параметра, то выводится ошибка.
*/
as $$
declare flag int default 0;
begin
select id_parameter from parameters where idPar = id_parameter into flag;
if flag is null then
raise notice 'This id does not exist. DB was not updated';
return 0;
else
delete from parameters where idPar = id_parameter;
return 1;
end if;
end $$ LANGUAGE plpgsql;
-- Вывести список значений параметров продуктов
CREATE OR REPLACE function show_product_parameters(idProduct integer)
RETURNS table (nameOfParameter text, valueOfParameter text, Uom text)
/*функция: Вывода парметров для заданного продукта
вход: idProduct- id продукта
выход: Таблица с найденными значениями
эффекты: 1. Если продукта с таким id нет, то будет выведена ошибка
*/
as $$
declare min_link int default 0;
declare count_params int default 0;
declare ind int default 0;
declare idpa int default 0;
declare val int default 0;
declare eval int default 0;
declare nagg int default 0;
declare namep text;
declare buf text;
begin
if (select id_link from product_parameters where idProduct = id_prod limit 1) is not null then
select min(id_link), count(*) from product_parameters where idProduct = id_prod into min_link, count_params ;
while ind != count_params loop
select id_par, value, id_enum_value, num_agg from product_parameters where idProduct = id_prod and min_link = id_link
into idpa, val,eval,nagg;
min_link:= min_link + 1;
if val is not null and eval is null and nagg is null then
select name_parameter from parameters where id_parameter = idpa into namep;
nameOfParameter:= namep;
select cast(val as text) into buf;
valueOfParameter:= buf;
select short_name_uom from unit_of_measure uom
where id_uom = (select uom_parameter from parameters where id_parameter = idpa)
into Uom;
return next;
ind:= ind + 1;
elsif val is null and eval is not null and nagg is null then
select name_parameter from parameters where id_parameter = idpa into namep;
nameOfParameter:= namep;
select name_value from enum_value where id_value = eval into buf;
valueOfParameter:= buf;
select short_name_uom from unit_of_measure uom
where id_uom = (select uom_parameter from parameters where id_parameter = idpa)
into Uom;
return next;
ind:= ind + 1;
elsif val is not null and eval is null and nagg is not null then
select id_par from aggregate_position where idpa = agg_par and nagg = num into idpa;
select name_parameter from parameters where id_parameter = idpa into namep;
nameOfParameter:= namep;
select cast(val as text) into buf;
valueOfParameter:= buf;
select short_name_uom from unit_of_measure uom
where id_uom = (select uom_parameter from parameters where id_parameter = idpa)
into Uom;
return next;
ind:= ind + 1;
elsif val is null and eval is not null and nagg is not null then
select id_par from aggregate_position where idpa = agg_par and nagg = num into idpa;
select name_parameter from parameters where id_parameter = idpa into namep;
nameOfParameter:= namep;
select name_value from enum_value where id_value = eval into buf;
valueOfParameter:= buf;
select short_name_uom from unit_of_measure uom
where id_uom = (select uom_parameter from parameters where id_parameter = idpa)
into Uom;
return next;
ind:= ind + 1;
end if;
end loop;
else
raise notice 'This product does not exist.';
nameOfParameter:= '0';
valueOfParameter:= '0';
return next;
end if;
end;
$$ language plpgsql;
-- Вывести список значений параметров классов
CREATE OR REPLACE function show_class_parameters(ClassId integer)
RETURNS table (nameOfParameter text, minV int, maxV int)
/*функция: Вывода парметров для заданного класса
вход: ClassId- id класса
выход: Таблица с найденными значениями
эффекты: 1. Если класса с таким id нет, то будет выведена ошибка
*/
as $$
declare min_link int;
declare count_params int;
declare minvl int;
declare maxvl int;
declare idpa int;
declare ind int default 0;
declare buf text;
begin
if (select id_link from class_parameters where id_class = ClassId limit 1) is not null then
select min(id_link), count(*) from class_parameters where ClassId = id_class into min_link, count_params ;
while ind != count_params loop
select id_par, minimum, maximum from class_parameters where ClassId = id_class and min_link = id_link
into idpa, minvl, maxvl;
min_link = min_link + 1;
if idpa is not null then
select name_parameter from parameters where id_parameter = idpa into buf;
nameOfParameter:= buf;
minV:= minvl;
maxV:= maxvl;
ind:= ind + 1;
end if;
return next;
end loop;
else
raise notice 'This class does not exist.';
nameOfParameter:= '0';
minV:= 0;
maxV:= 0;
return next;
end if;
end;
$$ language plpgsql;
-- Тесты / Добавление перечислений
--Добавление единицы измерения
SELECT add_uom('шт','штук/и');
SELECT add_uom('гр','грамм');
SELECT add_uom('стр','страниц/ы');
SELECT add_uom('мм','миллиметр/а');
SELECT add_uom('г','год');
SELECT add_uom('зв','звезд/ы');
--Добавление типов параметров
select add_type('Численный');
select add_type('Перечисление');
select add_type('Агрегат');
select add_type('Численный'); -- Попробуем добавить ещё раз
--Добавим классы
SELECT add_class('Книги', null, 1);
SELECT add_class('Художественная литература', 1, 1);
SELECT add_class('Фэнтези', 2, 1);
SELECT add_class('Детективы', 2, 1);
SELECT add_class('Приключения', 2, 1);
--Добавим несколько продуктов
SELECT add_product('Слоны умеют помнить', 4);
SELECT add_product('Настоящий британский детектив', 4);
SELECT add_product('Изгнанники', 5);
SELECT add_product('Айвенго', 5);
SELECT add_product('Большая телега', 3);
SELECT add_product('Кровь эльфов', 3);
--Добавим перечисления
SELECT add_enum('Авторы');
SELECT add_enum('Издательства');
SELECT add_enum('Переводчики');
SELECT add_enum('Авторы'); -- Попробуем создать вновь
-- Добавление значений перечислений
--Добавим авторов
SELECT add_value( 'Макс Фрай', 1);
SELECT add_value( 'Пратчетт Терри', 1);
SELECT add_value( 'Сапковский Анджей', 1);
SELECT add_value( 'Кристи Агата', 1);
SELECT add_value( 'Дойл Артур Конан', 1);
SELECT add_value( 'Лэкберг Камилла', 1);
SELECT add_value( 'Фексеус Хенрик', 1);
SELECT add_value( 'Скот Вальтер', 1);
SELECT add_value( 'Лондон Джек', 1);
SELECT add_value( 'Диккенз Чарльз', 1);
SELECT add_value( 'Честертон Гилберт Кит', 1);
SELECT add_value( 'Макс Фрай', 1); -- Добавим вновь
SELECT add_value( 'Россия', 4); --Добавим в несуществующее перечисление
-- Добавим Издательства
SELECT add_value( 'АСТ', 2);
SELECT add_value( 'Эксмо', 2);
SELECT add_value( 'Престиж БУК', 2);
SELECT add_value( 'Эксмо-Пресс', 2);
SELECT add_value( 'Вече', 2);
SELECT add_value( 'Альфа-книга', 2);
-- Добавим Переводчиков
SELECT add_value( 'Облонская Р.Е.', 3);
SELECT add_value( 'Озёрская Т.А.', 3);
SELECT add_value( 'Вершинина З.', 3);
SELECT add_value( 'Вайсброт Е.', 3);
SELECT add_value( 'Кононов М.В.', 3);
SELECT add_value( 'Бабков В.О.', 3);
SELECT add_value( 'Вольпин Н.Д.', 3);
SELECT add_value( 'Боченкова О.Б.', 3);
SELECT add_value( 'Скороденко В.А.', 3);
SELECT add_value( 'Злобина Г.П.', 3);
SELECT add_value( 'Бекетова Е.', 3);
--Добавим параметры
select add_parameter('Количество страниц', 1, uomPar:=3);
select add_parameter('Авторы', 2, idEnum:=1);
select add_parameter('Идательство', 2, idEnum:=2);
select add_parameter('Год издания', 1, uomPar:=5);
select add_parameter('Вес', 1, uomPar:=2);
select add_parameter('Переводчик', 2, idEnum:=3);
select add_parameter('Ширина', 1, uomPar:=4);
select add_parameter('Толщина', 1, uomPar:=4);
select add_parameter('Высота', 1, uomPar:=4);
select add_parameter('Рейтинг', 1, uomPar:=6);
select add_parameter('Популярность жанра', 1, uomPar:=6);
select add_parameter('Габариты', 3);
--Заполним таблицу параметров класса
select add_class_parameters(3,10,10,0); --Ограничение жанра по рейтингу
select add_class_parameters(3,1,10000,1); --Ограничение жанра на количество страниц
select add_class_parameters(4,10,10,0); --Ограничение жанра по рейтингу
select add_class_parameters(4,1,10000,1); --Ограничение жанра на количество страниц
select add_class_parameters(5,10,10,0); --Ограничение жанра по рейтингу
select add_class_parameters(5,1,10000,1); --Ограничение жанра на количество страниц
--select delete_class_parameters(4,1);
--Заполним таблицу агрегатов
select add_agg(11,7); -- Добавляем в агрегат ширину, высоту и длину
select add_agg(11,8);
select add_agg(11,9);
select add_agg(10,7); -- Попробуем добавить в неагрегат
select add_agg(19,7); -- Попробуем добавить в несущ. агр
select add_agg(11,19); -- Попробуем добавить несущ.парам.
-- Заполним таблицу параметров продуктов
--Слоны умеют помнить
select add_product_parameters(1,1,value:=288);
select add_product_parameters(1,2,id_enum_value:=4);
select add_product_parameters(1,3,id_enum_value:=15);
select add_product_parameters(1,4,value:=2022);
select add_product_parameters(1,5,value:=268);
select add_product_parameters(1,6,id_enum_value:=22);
select add_product_parameters(1,10,value:=10);
select add_product_parameters(1,11,value:=113, numAgg:=1);
select add_product_parameters(1,11,value:=17, numAgg:=2);
select add_product_parameters(1,11,value:=165, numAgg:=3);
--Настоящий британский детекив
select add_product_parameters(2,1,value:=544);
select add_product_parameters(2,2,id_enum_value:=5);
select add_product_parameters(2,2,id_enum_value:=10);
select add_product_parameters(2,2,id_enum_value:=11);
select add_product_parameters(2,3,id_enum_value:=15);
select add_product_parameters(2,4,value:=2021);
select add_product_parameters(2,5,value:=268);
select add_product_parameters(2,6,id_enum_value:=23);
select add_product_parameters(2,6,id_enum_value:=24);
select add_product_parameters(2,10,value:=8);
select add_product_parameters(2,11,value:=115, numAgg:=1);
select add_product_parameters(2,11,value:=22, numAgg:=2);
select add_product_parameters(2,11,value:=180, numAgg:=3);
--Изгнанники
select add_product_parameters(3,1,value:=512);
select add_product_parameters(3,2,id_enum_value:=5);
select add_product_parameters(3,3,id_enum_value:=16);
select add_product_parameters(3,4,value:=2021);
select add_product_parameters(3,5,value:=440);
select add_product_parameters(3,6,id_enum_value:=26);
select add_product_parameters(3,6,id_enum_value:=27);
select add_product_parameters(3,10,value:=9);
select add_product_parameters(3,11,value:=139, numAgg:=1);
select add_product_parameters(3,11,value:=28, numAgg:=2);
select add_product_parameters(3,11,value:=210, numAgg:=3);
--Айвенго
select add_product_parameters(4,1,value:=478);
select add_product_parameters(4,2,id_enum_value:=8);
select add_product_parameters(4,3,id_enum_value:=17);
select add_product_parameters(4,4,value:=2019);
select add_product_parameters(4,5,value:=754);
select add_product_parameters(4,6,id_enum_value:=28);
select add_product_parameters(4,10,value:=8);
select add_product_parameters(4,11,value:=171, numAgg:=1);
select add_product_parameters(4,11,value:=23, numAgg:=2);
select add_product_parameters(4,11,value:=241, numAgg:=3);
--Большая телега
select add_product_parameters(5,1,value:=512);
select add_product_parameters(5,2,id_enum_value:=1);
select add_product_parameters(5,3,id_enum_value:=12);
select add_product_parameters(5,4,value:=2022);
select add_product_parameters(5,5,value:=256);
select add_product_parameters(5,10,value:=7);
select add_product_parameters(5,11,value:=115, numAgg:=1);
select add_product_parameters(5,11,value:=31, numAgg:=2);
select add_product_parameters(5,11,value:=180, numAgg:=3);
--Кровь эльфов
select add_product_parameters(6,1,value:=320);
select add_product_parameters(6,2,id_enum_value:=3);
select add_product_parameters(6,3,id_enum_value:=12);
select add_product_parameters(6,4,value:=2022);
select add_product_parameters(6,5,value:=268);
select add_product_parameters(6,6,id_enum_value:=21);
select add_product_parameters(6,10,value:=10);
select add_product_parameters(6,11,value:=135, numAgg:=1);
select add_product_parameters(6,11,value:=22, numAgg:=2);
select add_product_parameters(6,11,value:=208, numAgg:=3);
select * from show_product_parameters(4); -- Посмотрим параметры Айвенго
select * from show_product_parameters(54); -- Посмотрим параметры того,чего нет
select * from show_class_parameters(4);