PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Declare / begin syntax when looping in PSQL

I’m trying to create 3 arrays, iterate through each, and insert records depending on the value of those iterations. My code is as follows:


do $$
  declare 
    days integer[];
    times time[];
    durations integer[];
  begin
    days := array[1,2, 3, 4, 5, 6, 7];
    times := array['00:00:00',
      '00:00:00',
      '01:00:00',
      '02:00:00',
      '03:00:00',
      '04:00:00',
      '05:00:00',
      '06:00:00',
      '07:00:00',
      '08:00:00',
      '09:00:00',
      '10:00:00',
      '11:00:00',
      '12:00:00',
      '13:00:00',
      '14:00:00',
      '15:00:00',
      '16:00:00',
      '17:00:00',
      '18:00:00',
      '19:00:00',
      '20:00:00',
      '21:00:00',
      '22:00:00',
      '23:00:00'
    ];
    durations := array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 24];
    FOR day IN days LOOP
      FOR time IN times LOOP
        FOR duration IN durations LOOP
          INSERT INTO public.pricing(id, site_id, start_time, price, day_of_week, booking_duration, inserted_at, updated_at) VALUES (10_000_000_000_000, 9999, time, 1000, day, duration, '2021-09-08 10:19:27.000000 +00:00', '2021-09-08 10:19:27.000000 +00:00');
        END LOOP;
      END LOOP;
    END LOOP;
  end;
$$;

When executing this I get

ERROR:  syntax error at or near "days"
LINE 35:     FOR day IN days LOOP

I’ve tried not using a variable, and instead doing

FOR day IN (1, 2, 3, 4, 5, 6) LOOP

But this throws the same error. I’ve tried following Looping through a given list of values in PL/pgSQL but can’t see where I’m going wrong… any help is greatly appreciated

Tags:

2

Answers


  1. 0

    You can use the unnest function to iterate over the result in a FOR loop using an ARRAY, for instance:

    do $$
  declare 
    days integer[];
    times time[];
    durations integer[];
   day  int;
   time time;
   duration int;
  begin
    days := array[1,2, 3, 4, 5, 6, 7];
    times := array['00:00:00',
      '00:00:00',
      '01:00:00',
      '02:00:00',
      '03:00:00',
      '04:00:00',
      '05:00:00',
      '06:00:00',
      '07:00:00',
      '08:00:00',
      '09:00:00',
      '10:00:00',
      '11:00:00',
      '12:00:00',
      '13:00:00',
      '14:00:00',
      '15:00:00',
      '16:00:00',
      '17:00:00',
      '18:00:00',
      '19:00:00',
      '20:00:00',
      '21:00:00',
      '22:00:00',
      '23:00:00'
    ];
    durations := array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 24];
    FOR day IN select unnest(days) LOOP
      FOR time IN select unnest(times) LOOP
        FOR duration IN select unnest(durations) LOOP
         raise notice 'day: %, time: %, duration: %',day,time,duration;
       END LOOP;
      END LOOP;
    END LOOP;
  end;
$$;

    you can use a foreach clause to iterate over an array as well

    Login or Signup to reply.
  2. 0

    See the documentation:

    FOREACH day IN ARRAY days LOOP
   ...
END LOOP;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search