Re: Issues with PL/PGSQL function..

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(at)accesswave(dot)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
>