“On commit drop” is generally good practice for several reasons: if your function is called in a loop, or the create
statementis in a loop or you want to call your function more than once in a given session (with out explicitly dropping
yourtemp table).
The commit in question is the function btw.
> On Jan 28, 2018, at 8:53 AM, Andy Colson <andy@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
addingand removing records. Yes, they will be vacuumed but vacuuming and bloat on catalog tables slows every single
querydown to some, degree since every query has to lookup its objects is those catalogs. Though caching probably
alleviatessome 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
aplpgsql function, do I have any other option than CREATE TEMPORARY TABLE? It didn't seem to work when I DECLAREd a
variableof 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
>