There is a very common technique used in other RDBMS (e.g. Sybase)
stored procedure programming which I am not sure how best to replicate
in Postgres functions.
A Sybase stored procedure can do
select <some complex query> into #temp1
create table #temp2 (.)
call some proc which also uses #temp1 and #temp2
etc
where #temp1 and #temp2 are temporary tables magically created by the
server for the duration of the procedure call only (the procedure can be
safely executed in parallel since each execution sees only its own
data). Under the hood, it does this by mangling the names of the temp
tables with a unique identifier for the procedure context.
What are the cleanest and most performant ways to do this in Postgres
(7.4.2)? I am aware of temporary tables but they are globally visible to
other invocations in the same session. We need the equivalent of local
method variables I guess.