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)
On 7/16/07, Chris Bowlby < excalibur@accesswave.ca> wrote: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?
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings