PostgreSQL - using variable inside quotes

user_991156 0
Viewed 97

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 ''
intervals plpgsql postgresql
2 Answers

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
user_3303150

a_horse_with_no_name's answer is, of course, correct, but I'd like to offer an alternative to round it out in case you're getting the string value dynamically (e.g., querying it, getting it as an argument from the user, etc). You could take the string value of '5 DAYS' and explicitly cast it to an interval using the :: operator:

IF (date(p_table_date) < date(NOW() - p_archive_depth::INTERVAL))
    -- Here -----------------------------------------^
user_24227760
Related