problem delete row in postgresql with commit

Question

I have a table who make a lot of time to delete some rows (32000 record by country). I create a function. I split the delete and use Loop instruction and commit but i have an error when i execute the function

My function DeleteMeasure :

CREATE OR REPLACE FUNCTION public.DeleteMeasure(
in_id_country integer)
RETURNS integer
LANGUAGE 'plpgsql'

COST 100
VOLATILE AS $BODY$ DECLARE
err_pgsql_message   TEXT;
_err_message        TEXT;
nbdelete INTEGER=0;
BEGIN   

LOOP
    
        DELETE FROM public.measure WHERE id_measure IN (SELECT id_measure FROM public.measure WHERE id_country=in_id_country LIMIT 1000);
        EXIT WHEN NOT FOUND;
        COMMIT;
END LOOP;

    
RETURN nbdelete;
END
$BODY$;

Select * from DeleteMeasure();

Return an error on COMMIT line. Can i have some help?

Answer 1

You should try the simple

delete from measure where id_country = 10;

Make sure that is_country is indexed before you delete. Selecting repeatedly 1000 items until there are no more matches slows down the process.

If you need to remove the majority of your records, then you could save the records you want to keep into another table whose schema is copied from this one, remove this table and then rename the other table.