How to declare and use a temporary table as a cursor source in PostgreSQL?
<p>Having normalized (mostly 2NF and some 3NF tables), I now need a query like below to climb up and down my table structure to get the information I need. (PostgreSQL 9.3)</p>

<pre><code>CREATE TEMPORARY TABLE test  AS
With charts_on_date_of_service AS ( 
    SELECT t.service_recid, t.tservice, t.chart_recid           
    FROM  ( select s.recid as service_recid, s.tservice, p.chart_recid
        from doctorservices d                   
        join services s on (s.recid = d.service_recid)      
        join patients p on (p.recid = s.patient_recid)      
        where s.tservice::date = _tservice::date        
        ) as t 
)
select s.recid as service_recid, s.tservice, c.chart_recid, c.tservice as time_of_service
from charts_on_date_of_service  c                   
join patients p on (p.chart_recid = c.chart_recid)          
join services s on (s.patient_recid = p.recid)              
join doctorservices d  on ( d.service_recid = s.recid)          
where s.tservice::date &lt;= _tservice::date               
order by c.chart_recid, s.tservice;                                    
</code></pre>

<p>As involved as this query is, I would like not to have to duplicate it within a plpgsql function. That is, I would like to do something like:</p>

<pre><code>CREATE OR REPLACE FUNCTION test(_tservice timestamp)            
  RETURNS TABLE (service_recid bigint, chart_recid int, tservice timestamp, ct int) AS
$func$ 

DECLARE 

CREATE TEMPORARY TABLE test AS ....   &lt;--THIS FAILS

cur CURSOR FOR
    SELECT t.service_recid, t.tservice, t.chart_recid   
    FROM   test t

BEGIN
      ... some processing commands on the temp table test and cursor cur....
END
</code></pre>

<p>I have several related questions:</p>

<ul>
<li>How is a temporary table "Declared" in plpgsql?</li>
<li>Can a temporary table be used as a source for a cursor?</li>
<li>Would performance be better creating a single temporary table and using it in multiple places, or better to recreate the identical query in multiple places?</li>
</ul>

<p>I can't seem to find the answers with Google. 
Any help or ideas is most appreciated.    </p>