In a postgres loop, will the delete actually be performed at the end of the loop?

I need to delete a table, but I want it to be really delete at the end of the loop.

Could you confirm that if I code this way, the table will actually be deleted at the end of the loop?

do $$
    DECLARE
      _result_id integer; 
begin
delete toto;
    FOR _result_id  select id from tata order by id LOOP
insert into toto (id) value (_result_id);
end loop;
end;
$$;

the tata table contains data that comes from a CSV, with the COPY. This table is updated every 24 hours.

So I have to sort and insert the data into the toto table. I want to empty the toto table and make the new insert from tata to toto. But I don’t want toto to be empty to prevent the website from returning any information.

thank you

Answer

You don’t need PL/pgSQL or an inefficient loop for this.

Just wrap the DELETE and INSERT into a single transaction without PL/pgSQL

begin transaction;
delete from toto;
insert into toto (id)
select id
from tata;
commit;

As this is a single transaction, changes are not seen by other transactions until you commit this. If anything fails, the transaction will be rolled back, including the DELETE and the toto table will look exactly like before.