Untitled

--get patient Details by Id
create or replace function getPatientById(id int)
returns table
(
Patient_id int,
FirstName varchar(50),
LastName varchar(50),
MiddleName varchar(50),
Gender varchar,
race varchar, 
chartNumber varchar(50),
dob date
)
as
$$
begin
	return query
	
select p.Patient_id ,
	   p.firstName,
	   p.LastName ,
	   p.MiddleName,
	   s.sex ,
	   r.race_type  , 
	   p.Chart_number,
	   p.dob 
from patient as p
left join sex_type as s
on p.sex_id = s.sex_id 
left join race_types as r 
on p.race_id = r.race_id
where p.patient_id = id
order by patient_id ;
	
end;
$$
language plpgsql;

select * from getPatientById(23)



-- Delete patient by id 
create or replace function deletePatientbyId (id int)
returns table(patientId int)
as $$
begin    
return QUERY delete from patient p where p.patient_id = id  returning p.patient_id;

end;
$$ 
language plpgsql;

-- to delete patient 
select * from deletePatientbyId(100);


-- Create patient
create or replace function createPatient(
FirstName varchar(50),
LastName varchar(50),
MiddleName varchar(50),
Sex_id int,
Race_id int,
Dob varchar
)
returns table(patientId int)
as
$$

begin
	return query
	insert into patient as p (FirstName,LastName,MiddleName,Sex_id,Race_id,Dob) values ($1,$2,$3,$4,$5,TO_DATE($6,'DD-MM-YYYY'))
	returning p.patient_id;
	
end;
$$
language plpgsql;

select * from createPatient('dhiraj','makwana','rameshbhai',1,2,'10-2-2002')



-- Update patient
create or replace function updatePatient(
id int,
fName varchar(50) default null,
lName varchar(50) default null,
mName varchar(50) default null,
Sex int default null,
Race int default null,
DateOfBirth varchar default null)
returns table(
patientId int
) 
as
$$
	declare str text;
begin
	str='update patient
	set patient_id=$1' || (case when fName is not null then ', firstname  = $2' else '' end)
		  || (case when lName is not null then ', LastName  = $3'  else '' end)
		  || (case when mName is not null then ', MiddleName  = $4' else '' end)
		  || (case when Sex is not null then ', Sex_id  = $5'  else '' end)
		  || (case when Race is not null then ', Race_id  = $6'   else '' end)
		  || (case when DateOfBirth is not null then ', Dob  =  TO_DATE($7,''DD-MM-YYYY'')'  else '' end)
		  ||
	' where patient.patient_id = $1
	returning patient.patient_id ';

return QUERY execute str using id,fName,lName,mName,Sex,Race,DateOfBirth;
end;
$$
language plpgsql;

-- to update patient.
select * from  updatePatient (id=>24,lname=>'patel',mName=>'dddd',Sex=>1,Race=>4,DateOfBirth=>'08-10-1990');

select * from  updatePatient (id=>23,fName=>'lklklk',lName=>'string',mName=>'string',Sex=>1,Race=>1,DateOfBirth=>'05-02-2002');


-- GetList With Pagination and sorting
create or replace function getPatientList(
	pagenumber int default 1,
	pagesize int default 5,
	sortby varchar default null
	)
	
	returns table(
	patientId int,
	firstName varchar,
	lastName varchar,
	middleName varchar,
	gender varchar,
	race varchar,
	chartNumber varchar,
	dob date
	) 
	as $$
	
	declare str text;
	
	begin
		str='select
		p.patient_id,
		p.firstname,
		p.lastname,
		p.middlename,
		sex,
		race_type,
		p.chart_number,
		p.DOB
	from
		patient as p
		inner join Sex_type as s on s.sex_id =p.sex_id 
		inner join race_types as r on r.race_id = p.race_id'
		||(case when sortby notnull then ' order by '|| $3 else ' order by p.patient_id ' end)
		||(' limit $2 offset ($1-1)*$2 ');
	return QUERY execute str using pagenumber, pagesize, sortby ;
	end $$
language plpgsql;

-- to get sort data
select * from getPatientList(sortby=>null,pagenumber=>1,pagesize=>9);