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.