This code correctly gathers diagnosis records for a patient to show distinct records based on the AMA ICD9 code, diagnosis description, and date of posting. The output is distinct on (ICD code, cdesc, tposted). The most recent date of usage, tposted, is assigned to each unique combination of (ICD9 code, cdesc).
Additionally, each record has flags as to whether it is a chronic condition, chronic, and/or part of the past medical history, PMH. A problem/diagnosis is considered current/active, if it has reoccurred since its last resolution, has never been resolved, is chronic, or has occurred on the date of patient encounter, tencounter. The code also includes the fact that people often have more then one name associated with their account and that the tables are at least 2NF with "recid" as foreign key reference to other tables.
My concerns are as follows:
- The CTE's defined before the
LOOPstatement all apply to each of the select statements of the union (good). However, within theLOOP...ENDLOOPsection, I am having to rewrite the "person" and "alldx" CTE for each associatedSELECTstatement. Is there any way to define these CTE's once, ideally in the first section and have enough permanence to be used in theLOOPsection and avoid defining the same CTE multiple times? - Would it be faster to create multiple views and then perhaps do left
joins or Updates to set the flags on each record as opposed to doing a loop on the output records? - Is there a better way to do this? How could this code be improved for speed?
CREATE OR REPLACE FUNCTION test (patient_recid integer, tencounter timestamp without time zone)
RETURNS SETOF view_dx AS
$BODY$
#variable_conflict use_column
DECLARE
r view_dx%rowtype;
BEGIN
-- will loop through all the records produced by the unions and assign tposted, pmh, chronic, etc...
FOR r IN
With person AS (
select p.chart_recid as recid
from patients p
where p.recid = patient_recid
)
, alldx AS (
select d.tposted, d.treated, d.resolved, d.recid as dx_recid, d.pmh, d.icd9_recid, i.code, i.cdesc, i.chronic
from dx d
join icd9 i on (d.icd9_recid = i.recid)
join person p on (d.chart_recid = p.recid)
where d.tposted::date <= tencounter::date
)
, dx_at_encounter AS ( -- get all diagnosis at time of encounter
select * from alldx a
where a.tposted::date = tencounter::date
)
, dx_resolved AS ( -- get most recent date of every resolved problem.
select b.* from alldx b
join (
select a.code, a.cdesc , max(tposted) as tposted
from alldx a
where a.resolved = true
group by code,cdesc) j
on (b.code = j.code and b.cdesc = j.cdesc and b.tposted = j.tposted)
)
, never_resolved AS ( -- get all problems that have never been resolved before time of encounter.
-- "not exists" is applied to each select output row AFTER the output row b.* is formed.
select b.* from alldx b
where not exists
(select 1
from dx_resolved d
where b.code = d.code and b.cdesc = d.cdesc)
)
, recurrent AS ( -- get all recurrent problems. (Problems that are now current after being resolved).
select b.*
from alldx b
join dx_resolved r on (b.cdesc = r.cdesc and b.tposted::date > r.tposted::date )
where (b.resolved is null or b.resolved = false)
)
, chronic_dx AS (
select b.*
from alldx b
where b.chronic = true
)
-- all diagnosis at time of encounter
select a.code,
a.cdesc
from dx_at_encounter a
union
-- all recurrent problems
select
a.code,
a.cdesc
from recurrent a
union
-- all problems that have never been resolved
select
a.code,
a.cdesc
from never_resolved a
union
--all chonic problems
select
a.code,
a.cdesc
from chronic_dx a
-- LOOP goes to END LOOP which returns back to LOOP to process each of the result records from the unions.
LOOP
r.tposted = ( -- get most recent useage of a diagnosis.
With person AS (
select p.chart_recid as recid
from patients p
where p.recid = patient_recid
)
, alldx AS (
select d.tposted, d.treated, d.resolved, d.recid as dx_recid, d.pmh, d.icd9_recid, i.code, i.cdesc, i.chronic
from dx d
join icd9 i on (d.icd9_recid = i.recid)
join person p on (d.chart_recid = p.recid)
where d.tposted::date <= tencounter::date
)
select max(b.tposted)
from alldx b
where r.cicd9 = b.code and r.cdesc = b.cdesc);
r.treated = (
With person AS (
select p.chart_recid as recid
from patients p
where p.recid = patient_recid
)
, alldx AS (
select d.tposted, d.treated, d.resolved, d.recid as dx_recid, d.pmh, d.icd9_recid, i.code, i.cdesc, i.chronic
from dx d
join icd9 i on (d.icd9_recid = i.recid)
join person p on (d.chart_recid = p.recid)
where d.tposted::date <= tencounter::date
)
select b.treated from alldx b
where b.tposted = r.tposted and b.code = r.cicd9 and b.cdesc = r.cdesc);
r.resolved = (
With person AS (
select p.chart_recid as recid
from patients p
where p.recid = patient_recid
)
, alldx AS (
select d.tposted, d.treated, d.resolved, d.recid as dx_recid, d.pmh, d.icd9_recid, i.code, i.cdesc, i.chronic
from dx d
join icd9 i on (d.icd9_recid = i.recid)
join person p on (d.chart_recid = p.recid)
where d.tposted::date <= tencounter::date
)
select b.resolved from alldx b
where b.tposted = r.tposted and b.code = r.cicd9 and b.cdesc = r.cdesc);
r.pmh = (
With person AS (
select p.chart_recid as recid
from patients p
where p.recid = patient_recid
)
, alldx AS (
select d.tposted, d.treated, d.resolved, d.recid as dx_recid, d.pmh, d.icd9_recid, i.code, i.cdesc, i.chronic
from dx d
join icd9 i on (d.icd9_recid = i.recid)
join person p on (d.chart_recid = p.recid)
where d.tposted::date <= tencounter::date
)
select distinct true
from alldx b
where
b.pmh = true and
b.code = r.cicd9 and
b.cdesc = r.cdesc );
r.chronic = (
With person AS (
select p.chart_recid as recid
from patients p
where p.recid = patient_recid
)
, alldx AS (
select d.tposted, d.treated, d.resolved, d.recid as dx_recid, d.pmh, d.icd9_recid, i.code, i.cdesc, i.chronic
from dx d
join icd9 i on (d.icd9_recid = i.recid)
join person p on (d.chart_recid = p.recid)
where d.tposted::date <= tencounter::date
)
select distinct true
from alldx b
where
b.chronic = true and
b.code = r.cicd9 and
b.cdesc = r.cdesc);
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION test(integer, timestamp without time zone)
OWNER TO postgres;
