How can rows be inserted or deleted in batches using a plpgsql script?

edit

To insert or delete rows in batches using a plpgsql script, you can use a loop to execute the insert or delete statement multiple times. Here is an example:

To insert rows in batches, you can use the following script:

CREATE OR REPLACE FUNCTION insert_batch(num_rows integer) RETURNS void AS $$
DECLARE
    i integer;
BEGIN
    FOR i IN 1..num_rows LOOP
        INSERT INTO my_table(column1, column2) VALUES (value1, value2); -- replace column1, column2, value1, value2 with actual values
        IF i % 1000 = 0 THEN -- commit after every 1000 rows
            COMMIT;
        END IF;
    END LOOP;
    COMMIT;
END;
$$ LANGUAGE plpgsql;

To delete rows in batches, you can use a similar script with a delete statement inside the loop:

CREATE OR REPLACE FUNCTION delete_batch(num_rows integer) RETURNS void AS $$
DECLARE
    i integer;
BEGIN
    FOR i IN 1..num_rows LOOP
        DELETE FROM my_table WHERE condition; -- replace condition with actual condition
        IF i % 1000 = 0 THEN -- commit after every 1000 rows
            COMMIT;
        END IF;
    END LOOP;
    COMMIT;
END;
$$ LANGUAGE plpgsql;

Note: you should adjust the batch size (1000 in the above examples) based on your available memory and the size of the table. Also, make sure to test these scripts on a small number of rows before executing on a large table.