postgres procedure giving ERROR: invalid input syntax for type timestamp: "select CURRENT_TIMESTAMP - INTERVAL '7 day'"

Question

I have created postgres procedure, I am not writing whole procedure here, Just writing the part which is giving error as belows,

create or replace procedure xyz() as $$
declare
  v_time timestamp without time zone;
  v_retain_days int;
Begin
  select retain_days from abc into v_retain_days;
  v_time := cast('select CURRENT_TIMESTAMP - INTERVAL ''' || v_retain_days || ' day''' as timestamp) ;
END;

With this procedure call, Its giving error as below:

ERROR: invalid input syntax for type timestamp: "select CURRENT_TIMESTAMP - INTERVAL '7 day'"

Can anyone help in this?

Answer 1

No need for a SELECT, dynamic SQL, casting or string concatenation:

v_time := CURRENT_TIMESTAMP - INTERVAL '1 day' * v_retain_days;

or alternatively:

v_time := CURRENT_TIMESTAMP - make_interval(days => v_retain_days);