In plpgsql (of PostgreSQL), can a CTE be preserved to an outer loop?

Question

(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

Answer 1

[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.

Answer 2

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;