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