Thread: Temp tables

Temp tables

From
"Andrew Bartley"
Date:
Hi all,
 
Can someone tell me what the difference is in a plpgsql function between dropping a temp table at the end of a function and not dropping the temp tables.
 
What other tables does it have an effect on?
 
It appears there is some overhead when the drop is being executed.
 
Is there some simple doco I can read to clue me up on the process?
 
Thanks
 
Andrew

Re: Temp tables

From
Bruce Momjian
Date:
Andrew Bartley wrote:
> Hi all,
>
> Can someone tell me what the difference is in a plpgsql function
> between dropping a temp table at the end of a function and not
> dropping the temp tables.
>
> What other tables does it have an effect on?
>
> It appears there is some overhead when the drop is being executed.
>
> Is there some simple doco I can read to clue me up on the process?

You can drop it whenever you want.  If not, it drops on session exit.

On issue with plpgsql is that when you create a temp table in a function
and drop it, then call the function again, it still think the original
temp table exists.  You need to use EXECUTE in plpgsql if you want to
drop/create temp tables multiple times in the same session.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073