Re: Temporary Tables

Joseph M. Day wrote:
> Nothing special about it other than "tmp_tblJoin" is defined as a
> temporary table.
>
> I do understand what is happening (I think). There is a stale pointer to
> the previous instance of the temp table (that no longer exists) which is
> causing the function to blow up. My question is how to I stop it from
> storing the OID of the old reference.

You don't - it caches plans for queries at compile-time (which is the
first time the function is run). You have to refer to temporary tables
using EXECUTE. Always. Alternatively, you could use a language that
doesn't cache query-plans, e.g. pltcl.

> I am relatively new to PG, but have years of experience with MSSQL and
> never had to deal with these type of issues. I am using plpqsql and
> explicitly setting it to volatile.

Volatile? What setting is that?

--
Richard Huxton
Archonet Ltd