title: How to declare and use a temporary table as a cursor source in PostgreSQL?
body: <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>