Re: Temporary Tables

Thanks, I thought there might be a way to force it not to do this.

So I guess for my example I am going to need to create another temporary
table to retrieve the results of my query, which of course I will also
have to be created via EXECUTE, since EXECUTE will not work in this
situation to store data in sTableName.

Any other more elegant ideas to retrieve the data from this?
-------------------
Select Into sTableName TableName From tmp_tblJoin Where ID = 1;

Thanks for the help!

Joe,

-----Original Message-----
From: Patrick(dot)FICHE(at)AQSACOM(dot)COM [mailto:Patrick(dot)FICHE(at)AQSACOM(dot)COM]
Sent: Thursday, March 31, 2005 10:06 AM
To: jday(at)gisolutions(dot)us; pgsql-general(at)postgresql(dot)org
Subject: RE: [GENERAL] Temporary Tables

You can find this in the FAQ

4.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
tél : 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