PostgreSQL - using variable inside quotes

Question

I am trying to use a variable for an expression like this

NOW() - INTERVAL '5 days'

But getting errors:

CREATE OR REPLACE FUNCTION some.archive() RETURNS VOID AS
$$
DECLARE
    p_archive_depth CONSTANT VARCHAR := '5 days';
BEGIN

    IF (date(p_table_date) < date(NOW() - INTERVAL p_archive_depth))
    THEN
        RAISE INFO '%', p_table_name;
    END IF;

END;
$$ LANGUAGE plpgsql;

Also tried without success:

'' || p_archive_depth || ''
'' p_archive_depth ''

Answer 1

You need to define the variable with the data type interval

DECLARE
    p_archive_depth CONSTANT interval := interval '5 days';
BEGIN 
   IF date(p_table_date) < (now() - p_archive_depth)::date