Stack Exchange
In plpgsql (of PostgreSQL), can a CTE be preserved to an outer loop?
(Edited from the original).
In plpgsql, (PostgreSQL 9.2), I have a function defined as:
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

FOR r IN 

With person AS (
    select ....
   )
, alldx AS (
    select ....
)
............

select  ... from first cte 
union 
select ... from second cte
union
etc., etc.,  

LOOP
    r.tposted = (       .
            With person AS (
                ... SAME AS ABOVE, 
                      alldx AS (
                ... SAME AS ABOVE,
            )
            select max(b.tposted)
            from alldx b
            where r.cicd9 = b.code and r.cdesc = b.cdesc);

    r.treated = (   
                With person AS (
                ........SAME AS ABOVE           )
                , alldx AS (
                ........SAME AS ABOVE   
                )
                select ...);

    r.resolved =  (     
                With person AS (
                select p.chart_recid as recid
                from patients p
                where p.recid = patient_recid
                )
                ...etc, etc, 

     RETURN NEXT r; 

END LOOP;

RETURN;

END

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION test(integer, timestamp without time zone)
  OWNER TO postgres;

Edit: Essentially, I have multiple cte's defined which work well in the "For r IN" section of code with multiple unions, but when executing the LOOP...END LOOP section, each CTE needs to be redefined with each SELECT statement. Is there a good way to avoid multiple definitions of the same CTE?
Or is there a better (i.e., faster) way of doing this.
All suggestions are most welcome and appreciated.
TIA

IIUC you can rid of the union: all the parts of the union are in fact SELECT code, cdesc, ... FROM alldx Which part of the union can be coded as bitfields: , EXISTS(__subquery__) AS is_final_diagnosis , or even as a LEFT JOIN (if additional columns are required)

There is just too much code here for a clearly defined question. Maybe a candidate for codereview.stackexchange.com/ ...

@ErwinBrandstetter  Please see above edit...I will shorten it. :)

@ErwinBrandstetter I added the code to codereview.stackexchange.com

Same question migrated to http://codereview.stackexchange.com/questions/109945/gathering-distinct-patient-diagnosis-records.

As far as I can tell, CTE's defined before the LOOP do not transfer to the LOOP itself. However, a temporary table can be defined in the BEGIN block which is available in the LOOP block. The following solution runs 50 times faster then my original code. Anybody have a better approach?
CREATE OR REPLACE FUNCTION test2 (patient_recid integer, tencounter timestamp without time zone)
  RETURNS SETOF view_dx AS
$BODY$ 

#variable_conflict use_column



DECLARE
    r view_dx%rowtype;


BEGIN
    -- create table can only be created in the BEGIN block
    Create temp table all_dx ON COMMIT DROP AS
    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 * from alldx order by tposted desc;    

-- will loop through all the records produced by the unions and assign tposted, pmh, chronic, etc...
FOR r IN 

With 
dx_at_encounter AS (        -- get all diagnosis at time of encounter
    select code, cdesc from all_dx a
    where a.tposted::date = tencounter::date
)
, dx_resolved AS (              -- get most recent date of every resolved problem.
    select b.* from all_dx b
    join (
        select a.code, a.cdesc , max(tposted) as tposted
        from all_dx 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.code, b.cdesc from all_dx 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.code, b.cdesc
    from all_dx 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.code, b.cdesc
    from all_dx 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.
            select max(b.tposted)
            from all_dx b
            where r.cicd9 = b.code and r.cdesc = b.cdesc);

    r.treated = (   
            select b.treated from all_dx b
            where b.tposted = r.tposted and b.code = r.cicd9 and b.cdesc = r.cdesc);

    r.resolved =  (     
            select b.resolved from all_dx b
                where b.tposted = r.tposted and b.code = r.cicd9 and b.cdesc = r.cdesc);

    r.pmh = (
            select distinct true 
            from all_dx b
            where
            b.pmh = true and 
            b.code = r.cicd9 and 
            b.cdesc = r.cdesc ); 

    r.chronic = (
            select distinct true 
            from all_dx 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 test2(integer, timestamp without time zone)
  OWNER TO postgres;


[this is not an answer (too little information, too large program), but a hint for rewriting the stacked CTE.]
The members of the union all appear to be based on select b.* from alldx b, all with a few different extra conditions, mostly based on the existance of other tuples within the same CTE. My suggestion is to unify these, replacing them by boolean flags, as 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
)
SELECT uni.tposted, uni.treated, uni.resolved, uni.dx_recid, uni.pmh, uni.icd9_recid
        , uni.code, uni.cdesc, uni.chronic
        , (uni.tposted::date = tencounter::date
                ) AS is_dx_at_encounter -- bitfield
        , EXISTS ( -- a record from a more recent date has resolved this problem.
                SELECT 1
                FROM alldx x
                WHERE x.resolved = true
                AND  uni.code = x.code AND uni.cdesc = x.cdesc AND uni.tposted = x.tposted
                AND x.tposted >= uni.tposted
                ) AS dx_resolved -- bitfield
        , EXISTS ( -- a record from a more recent date has resolved this problem.
                SELECT 1
                FROM alldx x
                WHERE x.resolved = false
                AND  uni.code = x.code AND uni.cdesc = x.cdesc AND uni.tposted = x.tposted
                AND x.tposted > uni.tposted
                ) AS dx_recurred -- bitfield
        , EXISTS ( SELECT * from alldx x where x.chronic = true
                AND uni.code = x.code AND uni.cdesc = x.cdesc
                ) AS dx_chronic -- bitfield
        -- etcetera
FROM alldx uni
        ;


The person CTE could probably be incorporated, too.
and maybe you don't even need the final loop
but you'll have to find out which combination(s) of the resulting bitfields will be needed.
the UNION (without ALL) in the original is a terrible beast: it collects all the results from the union parts, but has to remove duplicates. This will probably introduce a sort-step, since CTE-references tend to hide their key fields or implied ordering from the calling query.


I gather from your answer, that there is no way of preserving a CTE from before the LOOP...END LOOP and using it again in the LOOP section without defining it again... for each select statement in the loop ?? I wonder if a cursor/view would be any faster?? Thanks. :)
