How to avoid creating and dropping temp tables in functions? I have been creating some functions lately that run into the &quot;out of shared memory&quot; warning in postgres. I have tried to increase the max_locks_per_transaction, but it still occurs.
As far as i understand from the answer in this post (&quot;PostgreSQL complaining about shared memory, but shared memory seems to be OK&quot;) creating and dropping temp tables creates locks which can result in the earlier-mentioned warning.
for example:
create or replace function remove_vertices(par_geom geometry) 
returns geometry as $$ 
DECLARE 
cnt int;
BEGIN

drop table if exists invalid_pnts;
create temp table invalid_pnts as 
select * from selfintersects(par_geom) ; 
-- calls other function. selfintersects(par_geom) returns X rows with 4 columns/attributes, 
-- where X corresponds to number of faulty vertices in a postgis geometry
-- can return 0 rows   

select count(*) into cnt from invalid_pnts;
if cnt &gt; 0 
then -- do something with invalid_pnts table and the input &quot;par_geom&quot;
    
else 
    return par_geom 

end $$
language 'plpgsql';

Since this function is supposed to be called per row of a table (i.e. select  remove_vertices(geom) from some_table), the dropping and creation of the temp table can occur as many times as there are rows in a table.
What is an alternative to dropping/creating temp tables, if you need a &quot;table variable&quot; in a function?
Wouldn't a simple loop be enough?
create or replace function remove_vertices(par_geom geometry) 
returns geometry as $$ 
DECLARE 
  declare invalid_pnts record;
begin
  for invalid_pnts in select * from selfintersects(par_geom)
  loop
    -- do something with every record
  end loop;

  if not found then
    -- loop was empty
    return par_geom;
  end if;
end;
$$ language plpgsql;


Frequent creation of temporary tables will also bloat the system catalog and therefore slow down each and every query. It is really a bad idea to create and drop temporary tables (say a few tens per second) frequently in PostgreSQL.