Part 6 : CREATE PROCEDURE PostgreSQL

Task- Suppose we have two table product and sales  . If product table have Qty and Product data if customer pass product qty and pro-id so it will check product is available or not after that it will update sales list.

Task :- Create stored procedure for return multiple values 

call  fn_salary('IT',1,1); -- you have to passed defult values
-- DROP PROCEDURE fn_salary(character varying,integer)
create or replace procedure fn_salary (inout dept varchar,inout max_salary integer, inout avg_salary integer)

language plpgsql

as
$$
begin
select job,max(salary),avg(salary) into dept,max_salary,avg_salary
from employee where job='IT'
group by job;
end
$$

Task :- Create Store Procedure for insert data if table not exist in database

create or replace procedure sp_emp_data (id int)
language plpgsql
as
$$
begin
if EXISTS ( SELECT * FROM pg_catalog.pg_tables WHERE   tablename  = 'test1') then
insert into test1 select * from employees where employee_id=id;
else 
create table test1  as select * from employees where employee_id=id;
end if;
end
$$
drop table if exists test1;
call sp_emp_data(1);

Task 2 : Create store procedure stock update in table and check availability  

select * from sales; 

select * from producat;

call sp_update_stock(1,20)

create or replace procedure sp_update_stock(prod_id int,Qty_to_sales int)
language plpgsql
as $$
declare avl_prod int:=(select  qty from producat   where id=prod_id) ; v_result varchar;
  begin 
if avl_prod  < Qty_to_sales  then
      v_result:='Not enough stock';
raise  Exception '%',v_result;
end if;
update producat set qty=qty -Qty_to_sales where id=prod_id;
insert into sales (producatid,qtysold) values(prod_id,Qty_to_sales);
end $$

UI: Visual 

Query : - 

create procedure  fn_exception(val1 int,val2 int)
language plpgsql
as $$
declare v_result varchar;
begin
begin
v_result:=val1/val2;
exception when others then v_result:='Not zero';
end;
insert into test (name) values (v_result);
end;
$$