Thread: Issues with PL/PGSQL function..

Hi All,

 Running into a small issue with a PL/PGSQL function under PostgreSQL
8.0.11...

epassembly=# select version();
                                           version
---------------------------------------------------------------------------------------------
 PostgreSQL 8.0.11 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 3.3.3 (SuSE Linux)
(1 row)

The issue is most likely related to a "Drop table" call I have at the
end of the stored procedure, the relevant chunks of the code are (I
apologize in advance for culling as much as I have from the query's, but
I am unable to release those details):

BEGIN
 SELECT INTO ttl ''40''::integer AS id_days_ttl;

 CREATE TEMPORARY TABLE tmp1 AS SELECT ...;

 CREATE TEMPORARY TABLE tmp2 AS SELECT * FROM tmp1 ...;

 CREATE TEMPORARY TABLE tmp3 AS SELECT * FROM ... WHERE ... IN
(SELECT ... FROM tmp2);

 ...

 DROP TABLE tmp3;
 DROP TABLE tmp2;
 DROP TABLE tmp1;
END

The function runs the first time with out issue, but (and again I think
it's cause of the implicit drops in the function), I get this error on
any subsequent runs:

NOTICE:  Creating TEMPORARY table tmp1...
NOTICE:  Creating TEMPORARY table tmp2...
ERROR:  relation with OID 38699 does not exist

 I believe it's telling me that it can not find the OID of tmp1, but I
am unsure if it is looking for the first run value or the value of the
second run for that particular table...

 Does anyone have any additional suggestions that I can use to track
down more details of what is actually causing the issue?

Try using it with 'execute' as that might help...

OR:

CREATE TEMP TABLE tblname WITH (OIDS) ON COMMIT DROP AS select * from someothertbl;

that means the temporary table will be dropped at the end of the current transaction block.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

"Shoaib Mir" <shoaibmir@gmail.com> writes:
> Try using it with 'execute' as that might help...

In current releases you need EXECUTE, else the thing will try to cache a
query plan using the OID of the first instance of the temp table, and
that won't work for subsequent instances.

> OR:
> CREATE TEMP TABLE tblname WITH (OIDS) ON COMMIT DROP AS select * from
> someothertbl;

ON COMMIT DROP won't help, but maybe you could have just one temp table
per session, created with ON COMMIT DELETE ROWS?  Or try TRUNCATE'ing
the table when it already exists.

This will be all better in 8.3, FWIW.

            regards, tom lane