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

• Функция add_tovar

Функция для добавления товаров. Передаваемые параметры: индекс магазина, имя товара, цена товара, количество на складе, количество проданных товаров, имя производителя. При добавлении товара, который уже есть в таблице, новая запись не создаётся, а происходит увеличение количества этого товара на складе.

• Функция average_bill

Функция для вывода суммы среднего чека по магазину в разное время суток. Передаваемые параметры: индекс магазина, дата начало периода, дата конец периода.

• Функция rating

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

• Функция remove_tovar

Функция для удаления товаров. Удаление, если этот товар ни разу не был продан.

• Функция remove_shop

Функция удаления магазина из справочника. Удаление, если нет ни одного товара в данном магазине.

• Функция update_tovar

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

• Функция update_shop_catalog

Функция для обновления информации в справочнике магазинов. Передаваемые параметры: индекс магазина, новое имя магазина, новое имя города.

Выполнение работы

Создание групповых ролей:

create role dboperator nosuperuser inherit nocreatedb nocreaterole;

create role dbuser nosuperuser inherit nocreatedb nocreaterole;

grant dboperator to dbuser;

create role dbanalytic nosuperuser inherit nocreatedb nocreaterole;

create role dbadmin superuser inherit nocreatedb createrole;

grant dbanalytic to dbadmin;

grant dbuser to dbadmin;

grant dboperator to dbadmin;

Создание таблицы-справочника магазинов и назначение ей прав доступа, создание индекса по полю shop_name:

create table shop_catalog (id serial not null, shop_name character varying(40) not null, shop_city character varying(40) not null, constraint shop_id primary key (id) ) with (oids=false);

alter table shop_catalog owner to postgres;

grant all on table shop_catalog to postgres;

grant select, update, insert, delete on table shop_catalog to dboperator;

grant select on table shop_catalog to dbanalytic;

create index shop_name_btree on shop_catalog using btree (shop_name);

Создание таблицы с товарами и создание индекса по полю tovar_name и shop_id. Создание второго индекса в поле shop_id для связи с таблицей shop_catalog.

create table tovar ( id serial not null, shop_id integer, tovar_name character varying(40) not null, tovar_price integer, count_whs integer, count_sold_out integer, maker_name character varying(40) not null, arhiv boolean not null default false, constraint tovar_id primary key (id), constraint shop_id foreign key (shop_id) references shop_catalog (id) match simple on update no action on delete no action) with (oids=false);

alter table tovar owner to postgres;

grant all on table tovar to postgres;

grant select, update, insert, delete on table tovar to dboperator;

grant select on table tovar to dbanalytic;

create index tovar_name_btree on tovar using btree (tovar_name);

create index f_shop_id on tovar using btree (shop_id);

Создание таблицы с информацией о продажах и создание индекса по полю. Создание второго индекса в поле tovar_id, для связи с таблицей tovar.

create table sold_out ( id bigserial not null, buyer integer not null, shop_id integer not null, tovar_id integer not null, bought_count integer not null, buy_date timestamp without time zone not null default now(), constraint buyer_id primary key (id), constraint tovar_id foreign key (tovar_id) references tovar (id) match simple on update no action on delete no action ) with (oids=false);

alter table sold_out owner to postgres;

grant all on table sold_out to postgres;

grant select, update, insert, delete on table sold_out to dboperator;

grant select, update, insert on table sold_out to dbuser;

grant select on table sold_out to dbanalytic;

create index f_tovar_id_btree on sold_out using btree (tovar_id);