Retrieving a list of values ​in a loop for later use in the IN statement. Postgres

Question

How can you get a list of values ​​in a loop so that you can use it in the IN operator? It turns out that I need to declare the desired type, assign a list of values ​​to the variable and use it in the IN operator

 DO $$
declare
rec record;
v_id ?;--Declare the correct type
begin
  for rec in (select id_field 
                from my_tbl
                )
    loop
        
        v_id = rec.id_field; --This should be getting a list of values
    
    end loop; 
   
   for rec_new in (select new_id_field 
                from new_my_tbl
                where id_field in(v_id)--Here's the use of the resulting list
                )
    loop
        
        .....
    
    end loop; 
end;
$$ LANGUAGE plpgsql;

Answer 1

Loops are generally less performant than other methods. In this case you could define an array variable, populate it with targeted id_field values , then use it later with an IN statement.

Answer 2

Loops are always slower. Some years Tom Kyte, Senior Technical Architect (retired) at Oracle coined the phrase Slow by Slow processing. It is still appropriate. This is especially true here as this can be done in a single statement.

select new_id_field 
  from new_my_tbl
 where id_field in (select id_field 
                      from my_tbl
                   );

If you need the loop for additional processing then inbed that query in a cursor. See Documentation.