postgresqlprocedure

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


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;
$$;

Solution

  • 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 ;
    $$;