“On commit drop” is generally good practice for several reasons: if your function is called in a loop, or the create statement is in a loop or you want to call your function more than once in a given session (with out explicitly dropping your temp table). The commit in question is the function btw.
> On Jan 28, 2018, at 8:53 AM, Andy Colson <a...@squeakycode.net> wrote: > >> 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 >
