Untitled
plain_text
2 months ago
3.8 kB
1
Indexable
Never
--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);