Using a value from a for loop in a function

Question

Hi i am trying to create a function which loops from 1 to 12 and then use that loop value to calculate a date here is my code so far for the loop which is where the problem is.

FOR i IN 1..12 LOOP
    r.duedate := alert_start_period + interval 'i month';
    RAISE NOTICE 'Month % gives date of %', i, r.duedate;
END LOOP;

I know the problem is how i am referencing the value i. How do i correctly reference i from the loop so the interval is correct please.

The Error being thrown is

ERROR:  invalid input syntax for type interval: "i month"

Craig Ringer · Accepted Answer · 2014-01-22 09:21:09Z

3

You can't substitute a var into a string like that. Without using dynamic SQL, the cleanest way is a multiplier:

r.duedate := alert_start_period + i * INTERVAL '1' MONTH;

However, this whole idea is ugly. The loop is unnecessary. Use:

generate_series(alert_start_period, alert_start_period + INTERVAL '12' MONTH, INTERVAL '1' MONTH)

to generate the intervals. If you must, you can loop over that, but I generally find that everything not requiring exception handling is better done as normal set operations rather than pl/pgsql loops.

answered Jan 22, 2014 at 9:21

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

Collectives™ on Stack Overflow

Using a value from a for loop in a function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related