Re: Temp Table Within PLPGSQL Function - Something Awry

On Tue, Jan 16, 2007 at 11:10:25AM -0700, Lenorovitz, Joel wrote:
> Greetings,
>
> I am trying to work with a TEMP TABLE within a plpgsql function and I
> was wondering if anyone can explain why the function below, which is
> fine syntactically, will work as expected the first time it is called,
> but will err out as shown on subsequent calls.

Known problem, I beleive it's even mentioned in the docs.

Basically, if you use temp tables in pl/pgsql, you have to use EXECUTE
for the statements referring to it. The issue is that pl/pgsql is
caching the plan and so tries to use the plan with the old temp table
after the table has gone.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.