What I am not clear on is what the rules are as to when a function/procedure is effectively recompiled. Is there a danger that. assuming the temporary table is created for a session that one session might see another session's data due to the procedure having effectively compiled the temporary table into its definition?
it's not a problem - the plpgsql engine is implemented significantly differently than PL/SQL
plpgsql code is not compiled - it is just validated in "create" time and it is stored in original text form. When function is started first time in session, then source code is reparsed again to syntax tree, and this tree is evaluated. Inner SQL queries are prepared.
When you create and drop table, then prepared statements are automatically replaned.
Sometimes can be strange (and difficult) so plpgsql is similar to PL/SQL, but the implementation is maximally different - so some knowledges and experience are portable (and lot of related to internals) are not portable.
So far, I have not found a case where, as long as I don’t read or write to the permanent table, I get the wrong results from the above approach. It allows me to minimize the impact on my application (basically, it means that at the start of any transaction that might need a certain temporary table, I need to manually create it. The number of places I would need to do that is relatively finite, so I’m willing to take that hit, in exchange for not having to use dynamic SQL to refer to my temporary tables.
