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

begin

   select id from shop_catalog where id=$2 into tmp_shop_id;

   if (tmp_shop_id is null) then

                 return 'Ne nayden magazine po zadannomu ID';

   else

                 begin

                 select id, arhiv, shop_id from tovar where id=$3 into tmp_tovar_id, tmp_bool, tmp_shop_id;

                 if ((tmp_tovar_id is not null) and (tmp_bool=false) and (tmp_shop_id=$2)) then

                               begin

                               select count_whs from tovar where id=$3 into tmp_tovar_count;

                               if (tmp_tovar_count<$4) then

                                             return 'Na sklade net stolko tovara';

                               else

                                             begin

                                    insert into sold_out (buyer, shop_id, tovar_id, bought_count, buy_date)

                                             values($1,$2,$3,$4,$5);

                                             update tovar set count_whs=count_whs-$4 where id=$3;

                                             update tovar set count_sold_out=count_sold_out+$4 where id=$3;

                                    select buyer_id from buyers where buyer_id=$1 into tmp_buyer_id;

                                             select tovar_price from tovar where id=$3 into tmp_tovar_price;

                                             if (tmp_buyer_id is null) then

                                                            begin

                                                            tmp_bill=tmp_tovar_price*$4;

                                                            insert into buyers values($2,$1,tmp_bill,$5);

                                                            end;

                                             else

                                                            begin

                                                       select bill from buyers where buyer_id=$1 into tmp_bill_1;

                                                tmp_bill=tmp_bill_1+tmp_tovar_price*$4;

                                                            update buyers set bill=tmp_bill where buyer_id=$1;

                                                            end;

                                             end if;

                                  perform add_log_message('dobavlena informaciya po id "' || $1 || '"');

                                             return 'Informaciya dobavlena';

                                             end;

                               end if;

                               end;

                               else

                               return 'Ne naydeny tovary s zadannim id';

                 end if;

                 end;

   end if;

end

$body$

  language plpgsql volatile

  cost 100;

alter function add_sold_out(integer, integer, integer, integer, timestamp without time zone) owner to postgres;

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

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

grant execute on function add_sold_out(integer, integer, integer, integer, timestamp without time zone) to dboperator;

grant execute on function add_sold_out(integer, integer, integer, integer, timestamp without time zone) to dbuser;

Добавим новую продажу с номером чека 9, продаем в магазине с индексом 2, товар с индексом 1, в количестве 3-х штук в день 2012-10-28 09:51:21.

select add_sold_out(9,2,1,3,'2012-10-28 09:51:21');

Попытаемся продать товар которого не существует в передаваемом магазине:

select add_sold_out(9,2,7,3,'2012-10-28 09:51:21');

Во втором магазине нет товара с индексом 7.

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

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

select add_sold_out(9,2,7,25,'2012-10-28 09:51:21');