Postgres - How to surround parameter value with single quote

Question

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

Laurenz Albe · Accepted Answer · 2022-04-21 12:48:12Z

2

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

edited Apr 21, 2022 at 12:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Apr 21, 2022 at 11:51

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - How to surround parameter value with single quote

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related