Проектирование структуры базы данных сети магазинов продуктов питания, страница 2

      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';