create function logger(int, int, text) returns bool
as '
begin
INSERT INTO log (type, typeQuery, query, created) VALUES ($1, $2, $3, now());
Return true;
end;
' language 'plpgsql';
create function add_shop(varchar, varchar) returns char
as '
declare
t int;
temp bool;
begin
INSERT INTO shops (name, address) VALUES ($1, $2);
SELECT MAX(id) into t FROM shops;
select logger(0, 1, ''INSERT INTO shops (name, address) VALUES (''||$1||'', ''||$2||''); '') into temp;
return ''Add with id = '' || t;
end;
' language 'plpgsql';
create function add_product(varchar) returns char
as '
declare
t int;
begin
INSERT INTO products (name) VALUES ($1);
SELECT MAX(id) into t FROM products;
return ''Add with id = '' || t;
end;
' language 'plpgsql';
create function delete_product(int) returns char
as '
begin
delete from products where id = $1;
return Done!'';
end;
' language 'plpgsql';
create function delete_product(varchar) returns char
as '
begin
delete from products where name = ‘$1’;
return Done!'';
end;
' language 'plpgsql';
create function add_product_in_store(int, int, int, double) returns char
as '
declare
t int;
begin
INSERT INTO store (id_shop, id_product, kolvo, price) VALUES ($1, $2, $3, $4);
SELECT MAX(id) into t FROM store;
return ''Add with id = '' || t;
end;
' language 'plpgsql';
create function delete_product_from_store(int) returns char
as '
begin
delete from store where id = $1;
return ''Done!'';
end;
' language 'plpgsql';
create function delete_product_from_store(int, int) returns char
as '
begin
delete from store where id_shop = $1 and id_product = $2;
return ''Done!'';
end;
' language 'plpgsql';
create function create_cheque(int) returns char
as '
declare
t int;
begin
INSERT INTO cheque (id_shop, date) VALUES ($1, now());
SELECT MAX(id) into t FROM cheque;
return ''Add with id = '' || t;
end;
' language 'plpgsql';
create function delete_cheque(int) returns char
as '
begin
delete from cheque where id = $1;
delete from cheque_details where id_cheque = $1;
return ''Done!'';
end;
' language 'plpgsql';
create function add_in_cheque(int, int, int) returns char
as '
declare
t int;
kolvoFromTable int;
priceFromTable double;
begin
SELECT t1.kolvo into kolvoFromTable, t1.price into priceFromTable FROM store as t1, (select id_shop from cheque where id = $1) as t2 WHERE t1.id_shop = t2.id_shop AND id_product = $2;
if kolvoFromTable > $3 then
INSERT INTO cheque_details (id_cheque, id_product, kolvo, price) VALUES ($1, $2, $3, priceFromTable);
else
return ''Товар в таком количестве отсутствует на складе'';
end if;
SELECT MAX(id) into t FROM cheque_details;
return ''Add with id = '' || t;
end;
' language 'plpgsql';
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.