Функции в PostgreSQL. Проектирование структуры БД сети магазинов продуктов питания, страница 7

grant execute on function update_tovar(integer, integer, character varying, integer, integer, integer, character varying) to public;

grant execute on function update_tovar(integer, integer, character varying, integer, integer, integer, character varying) to postgres;

grant execute on function update_tovar(integer, integer, character varying, integer, integer, integer, character varying) to dboperator;

select update_tovar(1,2,'tapochki',560,40,4,'Makasa');

Обновление товара, которого нет на складе в заданном магазине:

select update_tovar(77,2,'tapochki',560,40,4,'Makasa');

Пример записи, имеющий архивный статус:

Обновление товара ‘tapochki’ во втором магазине. Изменим индекс магазина на 3:

select update_tovar(6,3,'tapochki',600,20,19,'Makasa');

Таким образом, товар tapochki с id=1 стал архивным, количество товара с id=6 было изменено на 20.

С архивными товарами ничего нельзя делать.

Создание таблицы для рейтинга товаров:

create table rating_t ( tovar_name character varying not null, maker_name character varying not null, rating float(2) ) with (oids=false);

alter table rating_t owner to postgres;

grant all on table rating_t to postgres;

grant all on table rating_t to dboperator;

grant all on table rating_t to dbanalytic;

grant all on table rating_t to dbuser;

Создание функции для вывода рейтинга товаров по магазинам.

Получает на вход номер магазина. Если получает номер не существующего магазина, то возвращает пустую таблицу.

create or replace function rating(integer)

  returns setof rating_t as

$body$

declare result "rating_t"%rowtype;

begin

   delete from rating_t;

   perform * from shop_catalog where id=$1;

   if found then

   for result in select tovar_name, maker_name, (count_sold_out/1.5) from tovar where (shop_id=$1) and (arhiv=false)

    order by count_sold_out desc loop

                 return next result;

   end loop;

   else

   return;

   end if;

end

$body$

  language plpgsql volatile

  cost 100

  rows 1000;

alter function rating(integer) owner to postgres;

grant execute on function rating(integer) to public;

grant execute on function rating(integer) to postgres;

grant execute on function rating(integer) to dbuser;

grant execute on function rating(integer) to dbanalytic;

select  * from rating(2);

Вывод суммы среднего чека по магазину в разное время суток:

create or replace function average_bill(integer,timestamp without time zone, timestamp without time zone)

  returns float as

$body$

declare

average_bill float(1);

begin

perform * from shop_catalog where id=$1;

if found then

              select avg(bill) from buyers where

              (buy_date between $2 and $3) and (shop_id=$1) into average_bill;

              return average_bill;

else

              return 0;

end if;

end

$body$

  language plpgsql volatile

  cost 100;

alter function average_bill(integer,timestamp without time zone, timestamp without time zone)

  owner to postgres;

grant execute on function average_bill(integer,timestamp without time zone, timestamp without time zone) to public;

grant execute on function average_bill(integer,timestamp without time zone, timestamp without time zone) to postgres;

grant execute on function average_bill(integer,timestamp without time zone, timestamp without time zone) to dbanalytic;

Выводим средний чек за промежуток времени 28 октября 2012 года между 9 утра и 20 часами во втором магазине.

Таблица с покупателями:

select * from buyers;

Средний чек за промежуток = (7200+1700+6800)\3= 5233,3

Запрос среднего чека:

select average_bill(2,'2012-10-28 09:00:00','2012-10-28 20:00:00');

Если попытаться отыскать сумму среднего чека в магазине, которого не существует, то функция вернет 0.

Обновление id втаблицах

alter sequence shop_catalog_id_seq start 1;

Выводы

В результате выполнения данной  работы были получены навыки работы с функциями с PostgreSQL, освоена методика построения цельной, высокопроизводительной базы данных, с использованием различных технологических особенностей PGSQL. Были закреплены и улучшены навыки, полученные в результате выполнения предыдущих лабораторных работ.