Re: Temporary Tables

You can find this in the FAQ

BM_4_264.26) Why can't I reliably create/drop temporary tables in PL/PgSQL
functions?

PL/PgSQL caches function contents, and an unfortunate side effect is that if
a PL/PgSQL function accesses a temporary table, and that table is later
dropped and recreated, and the function called again, the function will fail
because the cached function contents still point to the old temporary table.
The solution is to use EXECUTE for temporary table access in PL/PgSQL. This
will cause the query to be reparsed every time.

So as written, the best solution is to use EXECUTE for all queries using
temporary tables....

----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick(dot)fiche(at)aqsacom(dot)com
tel : 01 69 29 36 18
----------------------------------------------------------------------------
---------------

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Joseph M. Day
Sent: jeudi 31 mars 2005 17:50
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Temporary Tables

I am having some problems understanding how the temp tables work in PG. I
have a relatively lengthy function I am creating that makes frequent use of
temporary tables.

I am dropping and recreating the temp tables on each run. If I run the
procedure the first time via psql it seems to run fine. If I try to
immediately run it again I get the following message:

Error: relation with OID 22938 does not exist

The query it is running is the following:

Select Into sTableName TableName From tmp_tblJoin Where ID = 1;

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.

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.

Any help may save the last couple strands of hair on my head :-)

Joe,

--------------------------------------------
Joseph M. Day
Global Innovative Solutions