Phil Endecott wrote:
Dear PostgreSQL experts,
I have encountered a problem with temporary tables inside plpgsql functions. I suspect that this is a known issue; if someone could confirm and suggest a workaround I'd be grateful.
My function creates a couple of temporary tables, uses them, and drops them before returning:
create temporary table s as select 123 as id; create temporary table t ( id integer ); .... insert into t (select id from s); .... drop table s; drop table t; return;
When I run this the first time it works as expected. When I run it a second time I get this message:
ERROR: relation with OID 590209 does not exist CONTEXT: PL/pgSQL function "f" line 18 at SQL statement
(Line 18 is the insert-select statement.)
I imagine that it has cached that one of the tables is object 590209, but has not noticed that the table has been dropped and recreated before the second invokation of the function.
Your analysis is correct - Using execute is the work workaround.
While I'm all for function caching, this *feature* in the scenario above is verging on a bug as far as I am concerned. I think that this item should move from a "nice to have / to do" item to the bug list.
Other database systems that I have used are able to detect when a procedure/function need recompiling and do it on the fly. If postgres implemented this it would have another benefit. You could create objects that reference other objects that don't yet exist (or are not yet created) for example when applying a schema build script.
The first time the object is "used/called" it is compiled and all the dependencies are resolved then (this is what Oracle is able to do).
Not forcing SQL developers to use DB-specific workarounds would be a benefit too! :)
John Sidney-Woollett
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
