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.
I can supply a better test case if that would help.
Regards,
--Phil.
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
