Untitled
plain_text
2 months ago
5.6 kB
1
Indexable
Never
-- Patient table created create table patient( patientId serial not null primary key, firstName varchar(50) not null , lastName varchar(50) not null , dob date, isActive bool not null, createdData date, lastModifiedDate date ); ALTER TABLE patient ALTER COLUMN isActive SET default true; insert into patient (firstName, lastName, dob, isActive, createdDate, lastModifiedDate) values ('Rithin', 'Prem', '9-9-2000', true, '8-03-2001', '8-12-2001'); select * from Patient; -- AllergyMaster table created create table allergyMaster( allergyMasterId serial not null primary key, code varchar not null, name varchar not null, createdDate date, lastModifiedData date ); insert into allergymaster(Code,name,createdDate,lastModifiedDate) values('11BB','Pet Allergy','22-01-1952','10-06-1956'); -- PatientAllergy Table created create table patientAllergy( patientAllergyId serial not null primary key, patientId int not null, allergyMasterId int not null, note varchar, createDate date, lastModifiedDate date, foreign key(patientId) references patient(patientId) on delete cascade, foreign key(allergyMasterId) references allergyMaster(allergyMasterId) on delete cascade ); alter table allergyMaster rename column createDate to createddate; insert into patientAllergy(patientId,allergyMasterId,note,createdDate,lastModifiedDate) values (3,1,'serious','01-03-2002','21-02-2003') -- Create Patient. create or replace function createPatient( firstname varchar(50), lastname varchar(50), dob date, createddate date ) returns table(patientid int) as $$ begin return query insert into patient as p (firstName, lastName, dob, createdDate, lastModifiedDate) values (firstname,lastname,dob,createddate,createddate) returning p.patientid ; end; $$ language plpgsql; select * from createPatient('ram','sidhhpura','05-02-2002','25-04-2009'); -- Create patient allergy INSERT INTO patientAllergy ( patientid, allergymasterid, note, createddate, lastmodifieddate ) VALUES (7,3,'serious','05-02-2002','05-02-2002'), (7,1,'serious','05-02-2002','05-02-2002'); -- Get Patient details by id create or replace function getPatientById(pid int) returns table( patientid int, firstName varchar(50), lastName varchar(50), dob date, isActive bool, createdData date, lastModifiedDate date ) as $$ begin return query select p.patientid,p.firstName,p.lastName,p.dob,p.isActive,p.createddate ,p.lastmodifieddate from patient as p where p.patientid = pid; end; $$ language plpgsql; select * from getPatientById(3); -- Get Patient with allergy create or replace function getPatientByIdWithAllergy(pid int) returns table( patientid int, firstName varchar(50), lastName varchar(50), dob date, patientcreateddate date, patientlastmodifieddate date, allergymasterid int, name varchar, code varchar, note varchar, allergycreateddate date, allergylastmodifieddate date, isActive bool ) as $$ begin return query select p.patientid,p.firstName,p.lastName,p.dob,p.createddate ,p.lastmodifieddate ,p2.allergymasterid ,a.name ,a.code,p2.note,p2.createddate ,p2.lastmodifieddate ,p.isActive from patient as p left join patientallergy p2 on p.patientid = p2.patientid left join allergymaster a on p2.allergymasterid =a.allergymasterid where p.patientid = pid; end; $$ language plpgsql; select p.patientid,p.firstName,p.lastName,p.dob,p.isActive,p2.allergymasterid ,a.name ,a.code,p2.note from patient as p left join patientallergy p2 on p.patientid = p2.patientid left join allergymaster a on p2.allergymasterid =a.allergymasterid where p.patientid = 3; select * from getPatientByIdWithAllergy(3); -- Get Patient list create or replace function getPatientList( pagenumber int default 1, pagesize int default 5, sortby varchar default null ) returns table( patientid int, firstName varchar(50), lastName varchar(50), dob date, patientcreateddate date, patientlastmodifieddate date, allergymasterid int, name varchar, code varchar, note varchar, allergycreateddate date, allergylastmodifieddate date, isActive bool ) as $$ declare str text; begin str=' create table patientdata as select * from patient p limit 5 offset (1-1)*5; '||' \n '||' select p.patientid,p.firstName,p.lastName,p.dob,p.createddate ,p.lastmodifieddate ,p2.allergymasterid ,a.name ,a.code,p2.note,p2.createddate ,p2.lastmodifieddate ,p.isActive from patientdata as p left join patientallergy p2 on p.patientid = p2.patientid left join allergymaster a on p2.allergymasterid =a.allergymasterid ' ||(case when sortby notnull then ' order by '|| $3 else ' order by p.patient_id ' end) ||(' limit $2 offset ($1-1)*$2; ')||' \n ' ||'drop table patientdata;'; return QUERY execute str using pagenumber, pagesize, sortby ; end $$ language plpgsql; -- to get sort data select * from getPatientList(sortby=>'patientid',pagenumber=>1,pagesize=>9); create table patientdata as select * from patient p limit 5 offset (1-1)*5 drop table patientdata ; select p.patientid,p.firstName,p.lastName,p.dob,p.createddate ,p.lastmodifieddate ,p2.allergymasterid ,a.name ,a.code,p2.note,p2.createddate ,p2.lastmodifieddate ,p.isActive from patientdata as p left join patientallergy p2 on p.patientid = p2.patientid left join allergymaster a on p2.allergymasterid =a.allergymasterid