Untitled
unknown
plain_text
a year ago
7.9 kB
2
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, createdDate date, lastModifiedDate date ); ALTER TABLE patient ALTER COLUMN isActive SET default true; insert into patient (firstName, lastName, dob, isActive, createdDate, lastModifiedDate) values ('ram', 'patel', '05-6-2001', true, '01-06-2002', '01-10-2002'); select * from Patient; -- AllergyMaster table created create table allergyMaster( allergyMasterId serial not null primary key, code varchar not null unique, name varchar not null, createdDate date, lastModifiedDate date ); create table names( name varchar unique, id serial, phonenumber int); select * from allergymaster; select * from allergymaster; insert into allergymaster(Code,name,createdDate,lastModifiedDate) values('44bb','polen Allergy','02-01-1965','10-06-1970'); -- PatientAllergy Table created create table patientAllergy( patientAllergyId serial not null primary key, patientId int not null, allergyMasterId int not null, note varchar, createdDate 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; ALTER TABLE allergyMaster add PRIMARY KEY (patientID & ); select * from patientAllergy; insert into patientAllergy(patientId,allergyMasterId,note,createdDate,lastModifiedDate) values (4,2,'normal','01-02-2000','21-02-2001') -- 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 inner join patientallergy p2 on p.patientid = p2.patientid inner 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 query1 text; query2 text; query3 text; begin query3='drop table patientdata;'; execute query3; query1=' create table patientdata as select * from patient p limit $2 offset ($1-1)*$2;'; execute query1 using pagenumber, pagesize, sortby; query2='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); return QUERY execute query2 using pagenumber, pagesize, sortby ; end $$ language plpgsql; -- to get sort data select * from getPatientList(sortby=>'patientid',pagenumber=>1,pagesize=>1); 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 -- update patient details. create or replace function updatePatient( patientid int , firstname varchar, lastname varchar, dob date ) returns table( pid int ) as $$ declare query text; begin query='update patient set firstname=$2, lastname=$3, dob=$4, lastmodifieddate=current_date where patientid=$1; returning patientid'; return QUERY execute query using patientid,firstname,lastname,to_date(dob,'DD-MM-YYYY') ; end $$ language plpgsql; -- create patient allergy. create or replace function insertPatientAllergy( patientid int , allergymasterid int ) returns table( pid int ) as $$ declare query text; begin query='insert into patientallergy (patientid,allergymasterid,createddate,lastmodifieddate) values ($1,$2,current_date,current_date) returning patientid;'; return QUERY execute query using patientid,allergymasterid ; end $$ language plpgsql; --delete patient allergy create or replace function deletePatientAllergy( patientid int , allergymasterid int ) returns table( pid int ) as $$ declare query text; begin query='delete from patientallergy where patientid=$1 and allergimasterid=$2 returning patientid;'; return QUERY execute query using patientid,allergymasterid ; end $$ language plpgsql; --deactivate patient create or replace function deactivate( patientid int ) returns table( pid int ) as $$ declare query text; begin query='update patient set isactive=false where patientid=$1 returning patientid;'; return QUERY execute query using patientid ; end $$ language plpgsql; insert into patientallergy (patientid,allergymasterid,createddate,lastmodifieddate) values (4,2,current_date,lastmodifieddate=current_date); select * from patientallergy;