I want to delete data from the Postgres table which are older than n days. For which I am using the below-stored procedure, I want to pass n days via parameter, but it's failing at runtime.
CREATE OR REPLACE PROCEDURE usp_deletelog(DaystoDelete integer)
language plpgsql
as $$
DECLARE
maxcurrenttime timestamp;
begin
maxcurrenttime := (SELECT MAX(log_time_to_timestamp) FROM public.interface_execution_log_VJ);
RAISE NOTICE 'Value: %', maxcurrenttime;
DELETE FROM public.interface_execution_log_VJ
WHERE log_time_to_timestamp <= ( maxcurrenttime - '1 days'::interval); -- works if hardcoded i.e. 1, how to pass the parameter instead i.e. DaystoDelete with single quotes ?
commit;
end;$$;
CodePudding user response:
I prefer the function make_interval()
for that:
maxcurrenttime - make_interval(days => daystodelete);
Alternatively you can multiply a one day interval:
maxcurrenttime - interval '1 day' * daystodelete