PL/pgSQL遍历多数组插入行报错：循环语法错误排查求助

错误原因 #

你遇到的语法错误是因为PL/pgSQL的FOR ... IN语法默认仅支持遍历查询结果集，不能直接遍历数组变量或字面量列表。无论是FOR day IN days LOOP还是FOR day IN (1,2,3) LOOP的写法，都不符合PostgreSQL循环语法要求。

解决方案1：使用FOREACH遍历数组（推荐，PostgreSQL 9.1+支持） #

FOREACH是PL/pgSQL专门为遍历数组元素设计的语法，直接支持数组变量遍历。修改后的完整代码如下：

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];
    -- 用FOREACH替代FOR，指定遍历数组
    FOREACH day IN ARRAY days LOOP
      FOREACH time IN ARRAY times LOOP
        FOREACH duration IN ARRAY durations LOOP
          -- 注意：固定ID会导致主键冲突，建议替换为自增序列或生成唯一值
          INSERT INTO public.pricing(id, site_id, start_time, price, day_of_week, booking_duration, inserted_at, updated_at) 
          VALUES (nextval('pricing_id_seq'::regclass), 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;
$$;

解决方案2：用UNNEST将数组转为结果集遍历 #

如果使用的PostgreSQL版本低于9.1，可通过UNNEST函数把数组转换成查询结果集，再用FOR ... IN SELECT遍历：

do $$
  declare 
    days integer[];
    times time[];
    durations integer[];
    day integer;
    time time;
    duration 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];
    -- 用UNNEST将数组转为查询结果
    FOR day IN SELECT unnest(days) LOOP
      FOR time IN SELECT unnest(times) LOOP
        FOR duration IN SELECT unnest(durations) LOOP
          INSERT INTO public.pricing(id, site_id, start_time, price, day_of_week, booking_duration, inserted_at, updated_at) 
          VALUES (nextval('pricing_id_seq'::regclass), 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;
$$;

额外注意事项 #

• 原代码中id使用固定值10_000_000_000_000，多次插入会触发主键重复约束错误，必须替换为自增序列（如nextval('pricing_id_seq'::regclass)，需确保表对应序列存在）或生成唯一标识符（如gen_random_uuid()，若id是UUID类型）。
• 三层循环会生成大量记录（72513=2275条），执行前确认业务需求是否需要这么多数据。

内容的提问来源于stack exchange，提问作者Mark