ign="justify"> CREATE OR REPLACE FUNCTION "public". "OrdersHistory" (b_id integer, out "Name" varchar, out "BuyedCount" integer, out "DateOfBuy" date) RETURNS SETOF record AS
$ body $ _id INTEGER; _id INTEGER; a_id IN SELECT a.account_id FROM accounts a WHERE a.buyer_id = "b_id" LOOPr_id, "BuyedCount", "DateOfBuy" IN SELECT r. goods_id, r.reteil_count, r.date_of_reteil FROM reteil rr.account_id = a_id LOOPINTO "Name" g.name FROM goods g WHERE g.goods_id = r_id; NEXT; LOOP; LOOP;;
$ body $ 'plpgsql'ON NULL INPUTINVOKER100;
Результат роботи:
В
. TopBuyer (Пошук покупця, який купив товару на найбільшу суму)
CREATE OR REPLACE FUNCTION "public". "TopBuyer" (out "Lname" varchar, out "Total" double precision) RETURNS record AS
$ body $ double precision; id_g INTEGER; _a INTEGER; id_b INTEGER; disc INTEGER; double precision; per INTEGER; INTEGER; tmp double precision; varchar;
"Total": = 0; id_b, nname IN SELECT b.buyer_id, b.last_name FROM buyers b LOOP: = 0; id_a IN SELECT a.account_id FROM accounts a WHERE (a.buyer_id = id_b) LOOP id_g, disc, cnt IN SELECT r.goods_id, r.discount, r.reteil_count FROM reteil r WHERE (r.account_id = id_a) LOOPINTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g . goods_id = id_g);: = (seb + seb * per/100) * cnt;: = summ + tmp - tmp * disc/100; LOOP; LOOP; summ> "Total" then
"Total": = summ;
"Lname": = nname; if; LOOP;;
$ body $ 'plpgsql'ON NULL INPUTINVOKER100;
Результат роботи:
В
. PercentOfUsingShiping (Висновок статистики використовуваних способів доставки в процентному співвідношенні)
CREATE OR REPLACE FUNCTION "public". "PercentOfUsingShiping" (out "Type_of_shiping" varchar, out "Percentage" double precision) RETURNS SETOF record AS
$ body $ INTEGER; INTEGER; INTEGER; DOUBLE PRECISION; INTO cnt1 COUNT (a.type_of_shipping) FROM accounts a WHERE (upper (a.type_of_shipping) = upper ('Авіапошта')); INTO cnt2 COUNT (a.type_of_shipping) FROM accounts a WHERE (upper (a.type_of_shipping) = upper ('Наземна пошта')); INTO cnt3 COUNT (a.type_of_shipping) FROM accounts a WHERE (upper (a.type_of_shipping) = upper (' Кур'єр '));: = cnt1 + cnt2 + cnt3;
"Type_of_shiping": = 'Авіапошта';
"Percentage": = 100 * cnt1/summ; NEXT;
"Type_of_shiping": = 'Наземна пошта';
"Percentage": = 100 * cnt2/summ; NEXT;
"Type_of_shiping": = 'Кур'єр';
