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 ''

user330315 · Accepted Answer · 2021-09-17 11:13:43Z

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

answered Sep 17, 2021 at 11:13

user330315

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

Comments

Mureinik · Accepted Answer · 2021-09-17 11:17:04Z

1

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 -----------------------------------------^

answered Sep 17, 2021 at 11:17

Mureinik

316k54 gold badges399 silver badges405 bronze badges

1 Comment

holden321 Over a year ago

Thank you, a casting even better sometime, making code more readable. Also thank you for the correction of my poor English :)

Collectives™ on Stack Overflow

PostgreSQL - using variable inside quotes

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related