postgresql - 修复或避免在 postgresql 中使用 FOR 循环

Question

我正在尝试在 postgresql 中运行以下操作:

FOR realm_id IN (SELECT id from decision_realms WHERE id NOT IN (SELECT realm_id FROM voters WHERE realm_id IS NOT NULL)) 
LOOP
    INSERT INTO voters (voter_user_id, max_weight, actual_weight)
    SELECT contributor_user_id, pps, pps 
    FROM contributors WHERE 
        project_id= (SELECT project_id FROM goals WHERE id=(SELECT goal_id FROM decision_realms WHERE id=realm_id))
END LOOP;

它们都是独立工作的，这是:

 (SELECT id from decision_realms WHERE id NOT IN (SELECT realm_id FROM voters WHERE realm_id IS NOT NULL))

返回一列 id，并且

INSERT INTO voters (voter_user_id, max_weight, actual_weight)
SELECT contributor_user_id, pps, pps 
FROM contributors WHERE 
    project_id= (SELECT project_id FROM goals WHERE id=(SELECT goal_id FROM decision_realms WHERE id=1))

当 realm_id 硬编码为值 (1) 时，用预期的行填充选民表。

但是如果我尝试用一​​个变量替换值 1，并根据选择的结果在其上循环，则 for 循环不起作用。它说存在语法错误。

这可以不使用 FOR 循环来完成吗？或者我应该怎么写？

Answer 1

不优雅，未经测试(可能有错误)，但应该是这样

INSERT INTO voters (voter_user_id, max_weight, actual_weight)
SELECT contributor_user_id, pps, pps
from
(
    SELECT contributor_user_id, pps, pps 
    FROM contributors WHERE 
        project_id in (
            (SELECT project_id FROM goals WHERE id in (SELECT goal_id FROM decision_realms 
                WHERE id in (SELECT id from decision_realms WHERE id NOT IN (SELECT realm_id FROM voters WHERE realm_id IS NOT NULL)) 
                ))
)dec_realms

或者使用with语句

with dec_realms as
(
        SELECT contributor_user_id, pps, pps 
        FROM contributors WHERE 
            project_id in (
                SELECT project_id FROM goals WHERE id in (SELECT goal_id FROM decision_realms 
                    WHERE id in (SELECT id from decision_realms WHERE id NOT IN (SELECT realm_id FROM voters WHERE realm_id IS NOT NULL)) 
                    ))
)

INSERT INTO voters (voter_user_id, max_weight, actual_weight)
    SELECT contributor_user_id, pps, pps
    from dec_realms;

其他选择是像那样使用控制结构FOR

DO
$$
DECLARE
    _id integer;
BEGIN
    FOR _id IN (SELECT id from decision_realms WHERE id NOT IN (SELECT realm_id FROM voters WHERE realm_id IS NOT NULL)) 
    LOOP
        INSERT INTO voters (voter_user_id, max_weight, actual_weight)
        SELECT contributor_user_id, pps, pps 
        FROM contributors WHERE 
            project_id= (SELECT project_id FROM goals WHERE id=(SELECT goal_id FROM decision_realms WHERE id=_id));
    END LOOP;
END
$$

如果我犯了错误，我很抱歉，我确实缺少表结构。第一个和第二个选项可能会更快，因为 1 个操作而不是单独执行每个插入。