-- 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;