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

create index f_shop_id_btree on sold_out using btree (shop_id);

Создание таблицы (buyers) с информацией о каждой покупке (автоматическое заполнение).

create table buyers ( shop_id integer not null, buyer_id integer not null, bill integer not null, buy_date timestamp without time zone not null default now(), 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 buyers owner to postgres;

grant all on table buyers to postgres;

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

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

grant select on table buyers to dbanalytic;

create index f_buyer_id_btree on buyers using btree (buyer_id);

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

create table log_table ( id bigserial not null, cur_user character varying, "time" timestamp without time zone default now(), message text, constraint entry_id primary key (id ) ) with ( oids=false );

alter table log_table owner to postgres;

grant all on table log_table to postgres;

grant select, insert on table log_table to dboperator;

Создадим функцию, записывающую строку в лог-таблицу (log_table):

create or replace function add_log_message(character varying) returns void as $body$

begin

              insert into log_table(cur_user, message) values (current_user,$1);

              return;

end

$body$

  language plpgsql volatile

  cost 100;

alter function add_log_message(character varying) owner to postgres;

grant execute on function add_log_message(character varying) to public;

grant execute on function add_log_message(character varying) to postgres;

grant execute on function add_log_message(character varying) to dboperator;

select add_log_message('kakaya-to informacia');

Создание функции добавления магазина shop_add (таблица shop_catalog):

create or replace function add_shop(character varying, character varying) returns character as

$body$

declare id int;

begin

              insert into shop_catalog(shop_name, shop_city) values ($1,$2);

              select currval('shop_catalog_id_seq') into id;

              perform add_log_message('magazin "' || $1 || '" dobavlen po id ' || id);

              return 'Magazin dobavlen';

end

$body$

  language plpgsql volatile

  cost 100;

alter function add_shop(character varying, character varying) owner to postgres;

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

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

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

select add_shop('Maria-ra','Novosibirsk');

Создание функции удаления магазина из shop_catalog:

Удаление, если нет ни одного товара в данном магазине.

create or replace function remove_shop(integer)

returns character as

$body$

declare tmp_id int;

begin

              select id from shop_catalog where id=$1 into tmp_id;

              if (tmp_id is null) then

              return 'Net magazinov po zadannomu ID';

              else     

                            begin

                            delete from shop_catalog where id=$1;

                            select currval('shop_catalog_id_seq') into tmp_id;

                            perform add_log_message('magazin "' || $1 || '" udalen po id ' || tmp_id);

                            return 'Magazin udalen';

                            end;

              end if;

end

$body$

language plpgsql volatile

cost 100;

alter function remove_shop(integer) owner to postgres;

grant execute on function remove_shop(integer) to public;

grant execute on function remove_shop(integer) to postgres;

grant execute on function remove_shop(integer) to dboperator;

select remove_shop(3);

select remove_shop(1);

Создание функции добавления товара в таблицу tovar:

а) Возвращает сообщение, что товар был добавлен.

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