[SQL] CTEs and re-use - Mailing list pgsql-sql

I have a lovely little CTE/select doing exactly what I need it to do.  
Unfortunately I need its results in the next query. I have this in the 
function def below.

The gripe is that the function puts the results of the CTE/select into a 
temp table for the follow-on query.  That mean I have a name collision 
and have to drop the temp table.

I've tried in-lining the CTE/select put the performance is horrible. ( From 10 seconds (tolerable) to
over-a-minute-and-killedintolerable.  
 
The CTE is the long pole in the tent; running it standalone takes 9.9 
seconds).


What am I missing here in building the fence and losing the neighbours?

The CTE/select gives me the minimum value for all markers involved. The 
second part finds the "segment" from which that lowest p-value came, per 
marker. Then we reduce the list to distinct segment/p-value combinations.

create or replace function optimal_pvalue_set(people_name text, 
markers_name text, chr int)
returns table (segmentid uuid, optval numeric, firstbase int) as
$$
declare  mkset uuid;
begin  select id into mkset from seg.markerset where name = markers_name and 
chrom = chr;
  create temp table optmarkers as  with segset as (      select s.id             , s.chrom             , s.markerset_id
           , s.startbase             , s.endbase             , ((s.events_equal + s.events_greater)/(1.0 * 
 
(s.events_less + s.events_equal + s.events_greater))) as pval      from seg.segment s           join seg.probandset i
ons.probandset_id = i.id           join (select people_id, array_agg(person_id) as persons                 from
seg.people_member                group by people_id) as pa on i.probands <@ pa.persons           join seg.people o on
pa.people_id= o.id      where           s.markerset_id = mkset           and o.name = people_name  )  select m.id as
mkrid        , min(ss.pval) as optval  from segset ss       join seg.markerset_member mm on ss.markerset_id =
mm.markerset_id      join seg.marker m on mm.member_id = m.id  where       m.basepos between ss.startbase and
ss.endbase      and m.chrom = ss.chrom       and mm.markerset_id = mkset  group by m.id;
 
  return query  select s.id, o.optval, min(m.basepos) as firstbase  from optmarkers o  ---
<<<<----------------------------Tried
 
in-lining the CTE here.       join seg.marker m on o.mkrid = m.id       join seg.markerset_member mm on m.id =
mm.member_id      join seg.segment s on mm.markerset_id = s.markerset_id  where mm.markerset_id = mkset        and
m.baseposbetween s.startbase and s.endbase        and ((s.events_equal + s.events_greater)/(1.0 * (s.events_less 
 
+ s.events_equal + s.events_greater))) = o.optval  group by s.id, o.optval  order by firstbase;
end;
$$ language plpgsql;