Part 9 : How to use function in postgreSQL

Function is a set of SQL procedural commands like declarations, assignments, loops, flow-of-control etc. 

Function have return type and we can calculate & perform operation and return output  .

select  sum_n_prod(10,5)

create or replace function sum_n_prod(IN x int,IN y int,OUT sum int,OUT prod int)

language plpgsql

as 

$$

begin

IF x < 2 then

RAISE WARNING 'Information message %',now();

RAISE NOTICE 'Information message %',now();

RAISE INFO 'Information message %',now();

END IF;

sum := x + y;

prod := x * y;

end;

$$

Ex: Write function for calculate dept wise employees 

create function get_emp_details (emp_id  varchar(10))
returns int
language plpgsql
as
$$
declare
emp_data  integer ;
begin
select count(*)
into emp_data
from emp_details
where dept=emp_id ;
return emp_data;
end ;
$$

Ex-2 Create function for find max salary department

create or replace function fn_dept_higest_salary(dept_name text)
returns int
language plpgsql
as $$
declare D_salary integer ;
begin
select max(salary) into D_salary from emp_details where dept=dept_name group by dept;
return D_salary;
end ;
$$

Call function :-   select fn_dept_higest_salary('SALES')
 

Drop function :-  drop function fn_dept_higest_salary