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