The PostgreSQL FAQ currently suggests using dynamic SQL as a workaround
for the table OID caching problem of temp tables in pg/pgsql functions.
While this is ok, it fails to suggest that besides the initial
create/drop statements, every statement that touches the table must also
be dynamic.
With 8.0 comes pl/pgsql exception handlers...in the beginning of
function execution one might do the following:
begin
begin
delete from temp_table; -- temp table
exception
when others then
perform create temp temp_table [...]
end;
As long as the table structure does not change between function
executions, this can be a more elegant approach to dealing with this
problem. Pre 8.0, I would have suggested to initialize all temporary
tables in a special function, but this still requires special handling
code when the connection gets broken, etc. I think it would be helpful
to erstwhile pl/pgsql developers to list this alternative method here.
Merlin