How can I create procedure in PostgreSQL which will delete rows from table

Question

I want to create a procedure in PostgreSQL database which will delete the data older than 3 month from table and should commit after every day of deleting and then continue deleting until reaching the 3 month interval. I wrote sql but have trouble with procedure.

delete from testdrop where create_date<=current_date - interval '3 month';

Thanks in advance.

I created the procedure like below but I don't know how include here the committing after every day of deleting.

CREATE OR REPLACE PROCEDURE test_drop()
    LANGUAGE plpgsql
AS
$$
DECLARE
    c RECORD;
BEGIN
    FOR c IN ( delete
        from testdrop
     where create_date<=current_date - interval '3 month';
    )
END;
$$;

Answer 1

You have to create a list of dates you want to delete and execute each date in a single delete statement. You can use generate_series() to create the list of dates. Something like this should work:

CREATE OR REPLACE PROCEDURE test_drop()
    LANGUAGE plpgsql
AS
$$
DECLARE
    _r RECORD;
BEGIN
    FOR _r IN
        SELECT CAST(d AS DATE) d
        FROM GENERATE_SERIES(
                     (SELECT MIN(create_date) FROM testdrop) -- where to start
                 , CURRENT_TIMESTAMP - INTERVAL '3 months' -- where to end
                 , INTERVAL '1 day') gs(d)
        ORDER BY d
        LOOP
            DELETE
            FROM testdrop
            WHERE create_date >= _r.d
              AND create_date < (_r.d + INTERVAL '1 day'); -- delete single day
            RAISE NOTICE 'Date deleted: %', _r.d;
            COMMIT; -- commit just this single day
        END LOOP;
END ;
$$;