Информатика и выч. техника \ Базы данных

Функции в PostgreSQL. Проектирование структуры базы данных сети автосалонов

Страницы работы

18 страниц (Word-файл)
Посмотреть все страницы
Скачать файл

Фрагмент текста работы

NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

GRANT operator1 TO operator_r;

CREATE ROLE user_r LOGIN

ENCRYPTED PASSWORD 'md5d8ec2aab34309a2a3efc14239d66b121'

NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

GRANT user1 TO user_r;

Создание функций вывода с выдачей полномочий

CREATE OR REPLACE FUNCTION show_autoshows()

RETURNS SETOF autoshows AS

'BEGIN RETURN QUERY SELECT * FROM autoshows;END;'

LANGUAGE plpgsql VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION show_autoshows()

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION show_autoshows() TO admin1;

GRANT EXECUTE ON FUNCTION show_autoshows() TO analitik;

GRANT EXECUTE ON FUNCTION show_autoshows() TO user1;

CREATE OR REPLACE FUNCTION show_cars()

RETURNS SETOF autostock AS

'BEGIN RETURN QUERY SELECT * FROM autostock;END;'

LANGUAGE plpgsql VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION show_cars()

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION show_cars() TO admin1;

GRANT EXECUTE ON FUNCTION show_cars() TO analitik;

GRANT EXECUTE ON FUNCTION show_cars() TO user1;

CREATE OR REPLACE FUNCTION show_logs()

RETURNS SETOF logs AS

'BEGIN RETURN QUERY SELECT * FROM logs;END;'

LANGUAGE plpgsql VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION show_logs()

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION show_logs() TO admin1;

GRANT EXECUTE ON FUNCTION show_logs() TO analitik;

CREATE OR REPLACE FUNCTION show_managers()

RETURNS SETOF managers AS

'BEGIN RETURN QUERY SELECT * FROM managers;END;'

LANGUAGE plpgsql VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION show_managers()

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION show_managers() TO admin1;

GRANT EXECUTE ON FUNCTION show_managers() TO analitik;

GRANT EXECUTE ON FUNCTION show_managers() TO user1;

CREATE OR REPLACE FUNCTION show_sales()

RETURNS SETOF sales AS

'BEGIN RETURN QUERY SELECT * FROM sales;END;'

LANGUAGE plpgsql VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION show_sales()

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION show_sales() TO admin1;

GRANT EXECUTE ON FUNCTION show_sales() TO analitik;

GRANT EXECUTE ON FUNCTION show_sales() TO user1;

Создание функций вставки и удаления данных с выдачей полномочий

CREATE OR REPLACE FUNCTION add_autoshow(character, character)

RETURNS character AS

$BODY$

BEGIN

INSERT INTO autoshows(name, address) VALUES($1, $2);

INSERT INTO logs(action, date) VALUES('Создание новой записи в таблице autoshows', now());

RETURN 'Автосалон успешно добавлен';

END

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

ALTER FUNCTION add_autoshow(character, character)

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION add_autoshow(character, character) TO admin1;

GRANT EXECUTE ON FUNCTION add_autoshow(character, character) TO operator1;

CREATE OR REPLACE FUNCTION add_car(integer, character, character, integer)

RETURNS character AS

$BODY$

BEGIN

INSERT INTO autostock(asid, mark, model, price, instock) VALUES($1, $2,$3,$4,CAST(1 as bit));

INSERT INTO logs(action, date) VALUES('Создание новой записи в таблице autostock', now());

RETURN 'Машина успешно добавлена';

END

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

ALTER FUNCTION add_car(integer, character, character, integer)

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION add_car(integer, character, character, integer) TO admin1;

GRANT EXECUTE ON FUNCTION add_car(integer, character, character, integer) TO operator1;

CREATE OR REPLACE FUNCTION add_mananger(integer, character)

RETURNS character AS

$BODY$

BEGIN

INSERT INTO managers(as_id, name) VALUES($1, $2);

INSERT INTO logs(action, date) VALUES('Создание новой записи в таблице managers', now());

RETURN 'Работник успешно добавлен';

END

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

ALTER FUNCTION add_mananger(integer, character)

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION add_mananger(integer, character) TO admin1;

GRANT EXECUTE ON FUNCTION add_mananger(integer, character) TO operator1;

CREATE OR REPLACE FUNCTION add_sale(integer, integer)

RETURNS character AS

$BODY$

DECLARE mid int;

BEGIN

INSERT INTO sales(managerid, carid, date) VALUES($1, $2, now());

SELECT managers.as_id FROM managers where id = $1 INTO mid;

UPDATE autostock SET INSTOCK = CAST(0 as bit) WHERE id=$2 AND asid = mid;

INSERT INTO logs(action, date) VALUES('Создание новой записи в таблице sales', now());

RETURN 'Продажа успешно добавлена';

END

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

ALTER FUNCTION add_sale(integer, integer)

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION add_sale(integer, integer) TO admin1;

GRANT EXECUTE ON FUNCTION add_sale(integer, integer) TO operator1;

CREATE OR REPLACE FUNCTION delete_autoshow(integer)

RETURNS character varying AS

$BODY$

BEGIN

DELETE FROM autoshows WHERE id = $1;

RETURN 'Данные удалены';

END

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

ALTER FUNCTION delete_autoshow(integer)

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION delete_autoshow(integer) TO admin1;

GRANT EXECUTE ON FUNCTION delete_autoshow(integer) TO operator1;

CREATE OR REPLACE FUNCTION delete_car(integer)

RETURNS character varying AS

$BODY$

BEGIN

DELETE FROM autostock WHERE id = $1;

RETURN 'Данные удалены';

END

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

ALTER FUNCTION delete_car(integer)

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION delete_car(integer) TO admin1;

GRANT EXECUTE ON FUNCTION delete_car(integer) TO operator1;

CREATE OR REPLACE FUNCTION delete_manager(integer)

RETURNS character varying AS

$BODY$

BEGIN

DELETE FROM managers WHERE id = $1;

RETURN 'Данные удалены';

END

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

ALTER FUNCTION delete_manager(integer)

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION delete_manager(integer) TO admin1;

GRANT EXECUTE ON FUNCTION delete_manager(integer) TO operator1;

CREATE OR REPLACE FUNCTION delete_sale(integer)

RETURNS character varying AS

$BODY$

BEGIN

DELETE FROM sales WHERE id = $1;

RETURN 'Данные удалены';

END

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

ALTER FUNCTION delete_sale(integer)

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION delete_sale(integer) TO admin1;

GRANT EXECUTE ON FUNCTION delete_sale(integer) TO operator1;

Созданиефункцийповарианту

CREATE OR REPLACE FUNCTION raiting(character, character)

RETURNS SETOF testtype AS

$BODY$

BEGIN

RETURN QUERY SELECT managers.name, count(*) from autostock, managers, sales where autostock.mark=$1 AND autostock.model=$2 AND managers.id = sales.managerid and sales.carid = autostock.id GROUP BY managers.name;

END

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION raiting(character, character)

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION raiting(character, character) TO admin1;

GRANT EXECUTE ON FUNCTION raiting(character, character) TO analitik;

GRANT EXECUTE ON FUNCTION raiting(character, character) TO user1;

CREATE OR REPLACE FUNCTION recom()

RETURNS SETOF testtype2 AS

$BODY$

BEGIN

RETURN QUERY select zap.mark,zap.model,pop.count as popular,zap.count as zapas

from (

select autostock.mark, autostock.model, count(*)

from autostock where autostock.instock = CAST(1 as bit)

GROUP BY autostock.mark, autostock.model

ORDER BY count(*) DESC

) as zap,

(select autostock.mark, autostock.model, count(*)

from autostock, sales

where autostock.id = sales.carid

GROUP BY autostock.mark, autostock.model

) as pop

where zap.mark = pop.mark AND zap.model = pop.model ORDER BY pop.count DESC, zap.count DESC;

END

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION recom()

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION recom() TO admin1;

GRANT EXECUTE ON FUNCTION recom() TO analitik;

GRANT EXECUTE ON FUNCTION recom() TO user1;

Таблица доступа ролей входа к функциям

add_autoshow

add_manager

add_car

add_sale

raiting

show_logs

recom

show_autoshows

Пользователь

+

+

+

Аналитик

+

+

+

+

Оператор

+

+

+

+

Администратор

+

+

+

+

+

+

+

+

show_managers

show_cars

show_sales

delete_autoshow

delete_manager

delete_car

delete_sale

Пользователь

+

+

+

Аналитик

+

+

+

Оператор

+

+

+

+

Администратор

+

+

+

+

+

+

+

Табл. 1 Доступ групп пользоваталей к функциям базы данных

Проверка работоспособности функций

Функции вставки

Для того чтобы протестировать функции вставки, вставим в каждую таблицу по несколько значений, вызвав специальные функции.

select * from add_autoshow('Centr1', 'Mira 61');

select * from add_autoshow('Centr2', 'Mira 62');

select * from add_autoshow('Centr3', 'Mira 63');

select * from add_autoshow('Centr4', 'Mira 64');

Рис. 1 Результат выполнения запроса

select * from add_car(1,'Toyota', 'Corolla', 650000);

select * from add_car(1,'Toyota', 'Camry', 250689);

select * from add_car(1,'Toyota', 'Caldina', 112000);

select * from add_car(1,'Toyota', 'Mark2', 340000);

select * from add_car(2,'Toyota', 'Corolla', 123005);

select * from add_car(2,'Toyota', 'Camry', 2599054);

select * from add_car(2,'Toyota', 'Caldina', 950000);

select * from add_car(2,'Toyota', 'Mark2', 340000);

select * from add_car(3,'Toyota', 'Corolla', 290663);

select * from add_car(3,'Toyota', 'Corolla', 3600558);

select * from add_car(3,'Toyota', 'Caldina', 800555);

select * from add_car(3,'Toyota', 'Mark2', 560000);

select * from add_car(4,'Toyota', 'Camry', 112505);

select * from add_car(4,'Toyota', 'Camry', 960000);

select * from add_car(4,'Toyota', 'Caldina', 52000);

select * from add_car(4,'Toyota', 'Mark2', 90000);

Рис. 2 Результат выполнения запроса

select * from add_mananger(1, 'Kiril Orlov');

select * from add_mananger(2, 'Oleg Krupinov');

select * from add_mananger(4, 'Vadim Grach');

select * from add_mananger(4, 'Elena Karneeva');

Рис. 3 Результат выполнения запроса

select * from add_sale(1,2);

select * from add_sale(1,3);

select * from add_sale(1,4);

select * from add_sale(2,6);

select * from add_sale(2,8);

select * from add_sale(3,10);

select * from add_sale(3,11);

select * from add_sale(4,14);

select * from add_sale(4,15);

select * from add_sale(4,17);

Рис. 4 Результат выполнения запроса

select * from add_car(1,'q',25500);

Рис. 5 Результат выполнения запроса

Функции удаления данных

Вставим в таблицу autoshows новую запись после чего попробуем её удалить.

select * from add_autoshow('Kirka', 'qqq');

Рис. 6 Результат выполнения запроса

select * from delete_autoshow(6);

Рис. 6 Результат выполнения запроса

Рис. 7 Результат выполнения запроса

select * from add_car(1, 'qaz','zaq',15000);

Рис. 8 Результат выполнения запроса

select * from delete_car(19);

Рис. 8 Результат выполнения запроса

select * from add_mananger(1,'Orlovo');

select * from delete_manager(6);

Рис. 9 Результат выполнения запроса

select * from delete_sale(22);     

Рис. 10 Результат выполнения запроса

select * from delete_sale('q',456);

Рис. 11 Результат выполнения запроса

Функции просмотра данных

select * from show_autoshows();

Рис. 12 Результат выполнения запроса

select * from show_cars();

Рис. 13 Результат выполнения запроса

select * from show_logs();

Рис. 14 Результат выполнения запроса

select * from show_managers();

Рис. 15 Результат выполнения запроса

select * from show_sales();

Рис. 16 Результат выполнения запроса

Работа с пользователем

Для проверки работоспособности функций попробуем зайти под пользователем Оператор и выполним некоторые функции.

select * from show_sales();

Рис. 17 Результат выполнения запроса

select * from add_autoshow('qaz','Mira 61');

Рис. 18 Результат выполнения запроса

select * from delete_autoshow(8);

Рис. 19 Результат выполнения запроса

Создание и работа с функциями по варианту

CREATE OR REPLACE FUNCTION raiting(character, character)

RETURNS SETOF testtype AS

$BODY$

BEGIN

RETURN QUERY SELECT managers.name, count(*) from autostock, managers, sales where autostock.mark=$1 AND autostock.model=$2 AND managers.id = sales.managerid and sales.carid = autostock.id GROUP BY managers.name;

END

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION raiting(character, character)

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION raiting(character, character) TO admin1;

GRANT EXECUTE ON FUNCTION raiting(character, character) TO analitik;

GRANT EXECUTE ON FUNCTION raiting(character, character) TO user1;

select * from raiting('Toyota', 'Corolla');

Рис. 20 Результат выполнения запроса

CREATE OR REPLACE FUNCTION recom()

RETURNS SETOF testtype2 AS

$BODY$

BEGIN

RETURN QUERY select zap.mark,zap.model,pop.count as popular,zap.count as zapas

from (

select autostock.mark, autostock.model, count(*)

from autostock where autostock.instock = CAST(1 as bit)

GROUP BY autostock.mark, autostock.model

ORDER BY count(*) DESC

) as zap,

(select autostock.mark, autostock.model, count(*)

from autostock, sales

where autostock.id = sales.carid

GROUP BY autostock.mark, autostock.model

) as pop

where zap.mark = pop.mark AND zap.model = pop.model ORDER BY pop.count DESC, zap.count DESC;

END

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION recom()

OWNER TO postgres;

GRANT EXECUTE ON FUNCTION recom() TO admin1;

GRANT EXECUTE ON FUNCTION recom() TO analitik;

GRANT EXECUTE ON FUNCTION recom() TO user1;

select * from recom();

Рис. 21 Результат выполнения запроса

Заключение

В ходе выполнения лабораторной работы, были изучены способы создание функций на языке SQL, работа с ними а также разграничение полномочий

Похожие материалы

Информация о работе

ВУЗ:
Новосибирский государственный технический университет (НГТУ)
Предмет:
Базы данных
Тип:
Отчеты по лабораторным работам
Категория:
Информатика и выч. техника (Технические предметы)
Размер файла:
170 Kb
Скачали:
0
Скачать файл