Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

[Andy Colson]

On 01/28/2018 08:46 AM, Ryan Murphy wrote:

    I believe the main, and maybe only, concern is the bloating of the system 
catalog tables since you are constantly adding and removing records.  Yes, they 
will be vacuumed but vacuuming and bloat on catalog tables slows every single 
query down to some, degree since every query has to lookup its objects is those 
catalogs.  Though caching probably alleviates some of that


Yes, that's exactly the concern I heard, thanks for reminding me.

If I want to e.g. temporarily store a "setof records" or a "table" result in a variable as part of 
a calculation in a plpgsql function, do I have any other option than CREATE TEMPORARY TABLE?  It didn't seem to work 
when I DECLAREd a variable of type "setof table_name" or "setof table_name%rowtype", and then 
SELECT INTO it.

You may not need temp tables at all.  You can use subselects, derived tables, 
and cte's:

select sum(a+b) as total
from (
   select a, b+1
   from detail
) as tmpx;


This does the same thing as a temp table, with no temp table.

-Andy