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 <[EMAIL PROTECTED]> 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