PostgreSQL中如何循环执行指定插入SQL语句11次？

嘿，我来帮你搞定这个重复插入的需求！在PostgreSQL里，有两种简单且常用的方法可以实现重复执行你这条INSERT语句11次，下面给你详细说明：

方法一：使用PL/pgSQL匿名块（DO语句） #

你可以用PostgreSQL的PL/pgSQL编写一个匿名块，通过FOR循环明确控制执行次数。这种方法逻辑直观，适合需要清晰看到循环过程的场景：

DO $$
BEGIN
  FOR i IN 1..11 LOOP
    -- 放入你的INSERT语句
    Insert into Mark (id_student,mark,date,id_discteacher) 
    Select student.id_student,'10','2019-05-09',id_discteacher 
    from discipline_teacher 
    JOIN discipline using(id_discipline) 
    join teacher using(id_teacher) 
    join "group" on "class".id_group = discipline_teacher."group" 
    join student on student."group" = "group".id_group 
    where EXISTS (
        select * from discipline_teacher 
        join "group" on discipline_teacher."group" = "group".id_group 
        join student on student."group" = "group".id_group 
        JOIN discipline using(id_discipline) 
        join teacher using(id_teacher) 
        where discipline.title ='math' and teacher.id_teacher=1 and "group".title ='2' and "group".kurs ='А'
    ) and discipline.title ='math' and teacher.id_teacher=1 and "group".title ='2' and "group".kurs ='А' and student.name = 'Anna' and student.last_name ='Makeeva';
  END LOOP;
END $$;

注意：我把SQL里的group和class加了双引号，因为group是PostgreSQL的保留关键字，直接使用会触发语法错误，必须用双引号包裹来表示表名。

方法二：使用generate_series简化实现 #

如果你不需要显式的循环逻辑，还可以利用PostgreSQL的generate_series函数生成11行数据，通过交叉连接让原查询结果重复11次，代码更简洁：

Insert into Mark (id_student,mark,date,id_discteacher) 
Select student.id_student,'10','2019-05-09',id_discteacher 
from discipline_teacher 
JOIN discipline using(id_discipline) 
join teacher using(id_teacher) 
join "group" on "class".id_group = discipline_teacher."group" 
join student on student."group" = "group".id_group 
cross join generate_series(1,11) -- 生成11次重复的查询结果
where EXISTS (
    select * from discipline_teacher 
    join "group" on discipline_teacher."group" = "group".id_group 
    join student on student."group" = "group".id_group 
    JOIN discipline using(id_discipline) 
    join teacher using(id_teacher) 
    where discipline.title ='math' and teacher.id_teacher=1 and "group".title ='2' and "group".kurs ='А'
) and discipline.title ='math' and teacher.id_teacher=1 and "group".title ='2' and "group".kurs ='А' and student.name = 'Anna' and student.last_name ='Makeeva';

这个方法的原理是通过cross join generate_series(1,11)把原查询的结果复制11份，从而一次性完成插入操作（如果原查询返回N条记录，这里会插入N*11条）。

额外提醒 #

• 如果你插入的记录可能违反Mark表的唯一约束（比如id_student和id_discteacher的组合是唯一键），循环插入会触发报错，这时候你需要添加ON CONFLICT子句处理冲突，或者确认业务逻辑允许重复插入这些记录。
• 执行前建议单独运行一次原INSERT语句，确认它能返回你想要插入的正确记录，避免循环执行后插入错误数据。

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