title: Drop temporary table when exiting a function
body: <p>I use a temp table in a function with the 'on commit drop' option. My problem is, in certain cases, a more global function can call the first one twice, so the "create temp table" is called twice before the commit - so I have the normal error "relation [my_temp_table] already exists".</p>
  
  <p>I use the temp table at the end of the function to return its rows in the "return query", so I can't manually drop the table before I leave the function.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION my_function(_value text)
  RETURNS setof my_table AS $$
  DECLARE 
      resultCount integer := 0;
  BEGIN
  
      create temp table my_temp_table on commit drop as
      select *
      from my_table 
      where value = _value ;
  
      select count(*) into resultCount from my_temp_table;
      if (resultCount = 0) then 
          raise exception 'value not found';
          end if;
  
      return query
      select * from my_temp_table;
  
  END;$$ LANGUAGE plpgsql VOLATILE COST 100;
  ALTER FUNCTION my_function(text) OWNER TO postgres
  </code></pre>
  
  <p>If you wonder why I use a temp table and not my_table in a direct way it's because I need a really fast response and my_table is very big (several dozens of millions rows) so this way I can request it only once instead of three times (search, count and return).</p>
  
  <p>I found a workaround not using temp table and creating a type, but the structure of my_table will change many times, and actually I have dozens of "my table" and the concerned "my function", so this was a way to not write again all my functions each time the structure of my tables will change.</p>
  
  <p>The function has to return the same structure than the table it requests.</p>
  
  <p>How can I can drop the table when leaving the function? Or is there a better workaround?</p>