There are three problems with using PostgreSQL temporary tables:
1. PL/pgSQL will cache the OID of the temporary table that existed when it is first parsed, and when that temporary table is dropped and recreated later, despite having the same name and structure, you'll get an error like:
ERROR: relation with OID 869140 does not exist CONTEXT: PL/pgSQL function "mytest" line 4 at select into variables
The work-around is to use EXECUTE to build the query string at run-time.
I should point out that if you ensure that you create the temporary table before invoking the function, the function gets parsed once after the first invocation at the beginning of the session. So that, in that instance, it would be okay.
Mike Mascari
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
