CODEWITHSUNDEEP
postgresqlsyntaxsyntax-errorplpgsql
Abhi
Abhi

Reputation: 429

PGSQL: Adding a variable interval - Syntax error

I have a quick question. I am fairly new to pgsql and I am unable to figure out how to fix the syntax error below.

Here is what I am trying to do 

start_date := '2011-01-01'::date;
end_date   := '2011-03-01'::date;
duration := '6 months'

    while start_date < end_date loop
        window_start_date = start_date;
        window_end_date = window_start_date + interval||duration||;

    end loop;

However I keep getting a syntax error. 

ERROR:  column "interval" does not exist
LINE 1: SELECT   $1  + interval|| $2 ||
                       ^
QUERY:  SELECT   $1  + interval|| $2 ||

What am I doing wrong. Any help would be much appreciated

Upvotes: 1

Views: 1729

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658937

Guesswork (the rest of the function definition is missing).
This would work in PL/pgSQL (which are using behind the curtains):

window_end_date := window_start_date + interval duration;

Or:

window_end_date := window_start_date + duration::interval;

  • Cast the text value to interval to make it work. But it would be better to declare the variable duration as interval to begin with (maybe that is the case, then drop the cast - information missing).

  • The assignment operator in plpgsql is :=, not =.

  • The result is a timestamp, not a date. But it will be coerced to date in your example.

Upvotes: 3

Related Questions