I have PL/PGSQL functions that require temporary tables for storing intermediate query output. I used to create a temporary table with a unique name in each function call, but that led to "out of shared memory" errors and bloating of system catalogs, because temp tables are only actually dropped at the end of a transaction, as Tom pointed out. So I tried creating non-temporary tables specifically for use in functions, but the result was that concurrency was not possible as each transaction using the function would retain a lock on the table. I then considered creating temporary tables once per session or transaction. Considering that a connection pool might be used, I had to make my temp tables "ON COMMIT DROP". I still have the following worries: - not very friendly (must call a 'init' function at beginning of each transaction) - must wrap all queries in EXECUTE statements - ON COMMIT DROP may not be portable to other DBMS (not too important here) - may also lead to system catalog bloating over time. Overall I find it rather troublesome to use temporary storage tables in functions. Maybe I am missing something. Any tips?
