On Wed, 2006-09-20 at 17:29 -0400, Terry Lee Tucker wrote:
> Well, I was assuming that that the table wasn't being dropped and that was
> what was causing the error. I can see from your comments, that I was wrong on
> that asssumption. I can do this with and execute, but it's going to be a pain
> to acomplish. I wonder what good a temporary table is if you can't use the
> code which creates it twice in a row with reloading the function?
>
Well, the problem is not with temporary tables so much as the cached
plans. PL/pgSQL decides when the function is first run that the
temporary table you're using has OID 123654 (or whatever), and rather
than using the table name on the function call, it assumes that the OID
has not changed.
So, a temporary table is still useful for any situation where it doesn't
cache the query plan (like a normal query, or an EXECUTE inside
PL/pgSQL).
But yes, it is frustrating, and will hopefully be fixed in later
versions.
Regards,
Jeff Davis