我尝试使用PL/pgsql循环更新一个分区的PostgreSQL表，以限制每次只更新一个分区，因为它包含一个大表上的自连接。我一直得到错误“太多的范围表条目”，我不明白为什么。如果我在WHERE子句中对分区进行硬编码以进行更新，那么即使我使用IN（）同时运行10个分区，它也能正常工作。
我还以为他们俩做的是同一件事依次为city_id的每个值循环UPDATE。这样不对吗我怎么才能让它做到这一点？
这不起作用：

DO
$$
DECLARE
    ele record;
BEGIN
    FOR ele IN SELECT city_id 
           FROM dwh.city
    LOOP 
        UPDATE dwh.residence res1
        SET residence_count = res2.residence_count
        FROM (
            SELECT 
                block_id, 
                COUNT(*) as residence_count,
            FROM dwh.residence  
            WHERE 1=1
                AND city_id = ele.city_id
            GROUP BY block_id
        ) res2
        WHERE 1=1
            AND res1.city_id = ele.city_id
            AND res1.block_id = res2.block_id;
    END LOOP;
END;
$$;

但是，这确实有效：

UPDATE dwh.residence res1
SET residence_count = res2.residence_count
FROM (
    SELECT 
        block_id, 
        COUNT(*) as residence_count,
   FROM dwh.residence  
   WHERE 1=1
       AND city_id = 1
  GROUP BY block_id
) res2
WHERE 1=1
    AND res1.city_id = 1
    AND res1.block_id = res2.block_id;