postgresql - 如何在 PL/pgSQL 中将 SELECT 嵌套到 UPDATE 语句中

Question

我有下面的代码运行良好，问题是我每次都创建一个表，这意味着我需要重新创建所有索引并在创建新表时删除旧表。

DO
$do$
DECLARE
    m   text;
    arr text[] := array['e09000001','e09000007','e09000033','e09000019'];
BEGIN
    FOREACH m IN ARRAY arr
    LOOP
        EXECUTE format($fmt$
            CREATE TABLE %I AS
            SELECT a.ogc_fid,
                   a.poly_id,
                   a.title_no,
                   a.wkb_geometry,
                   a.distcode,                 
                   SUM(COALESCE((ST_Area(ST_Intersection(a.wkb_geometry, b.wkb_geometry))/ST_Area(a.wkb_geometry))*100, 0)) AS aw
            FROM %I a
                LEFT OUTER JOIN filter_ancientwoodlands b ON
                    ST_Overlaps(a.wkb_geometry, b.wkb_geometry) OR ST_Within(b.wkb_geometry, a.wkb_geometry)
            GROUP BY a.ogc_fid,
                     a.poly_id,
                     a.title_no,
                     a.wkb_geometry,
                     a.distcode;
        $fmt$, m || '_splitv2_aw', m || '_splitv2_distcode');
    END LOOP;
END
$do$

相反，我只想在现有表中创建一个新列并更新它。我已经通过简单的查询完成了此操作，例如:

ALTER TABLE e09000001 ADD COLUMN area double precision;
UPDATE e09000001 SET area=ST_AREA(wkb_geometry);

我在弄清楚如何将 UPDATE 和 SET 与上面更复杂的 SELECT 语句一起使用时遇到了很多麻烦。有谁知道我怎样才能做到这一点？

更新:所以我尝试按照@abelisto 的建议进行操作:

UPDATE test_table
SET aw = subquery.aw_temp
FROM (SELECT SUM(COALESCE((ST_Area(ST_Intersection(a.wkb_geometry, b.wkb_geometry))/ST_Area(a.wkb_geometry))*100, 0)) AS aw_temp
            FROM test_table a
                LEFT OUTER JOIN filter_ancientwoodlands b ON
                    ST_Overlaps(a.wkb_geometry, b.wkb_geometry) OR ST_Within(b.wkb_geometry, a.wkb_geometry)
            GROUP BY a.ogc_fid,
                     a.poly_id,
                     a.title_no,
                     a.wkb_geometry,
                     a.distcode) AS subquery;

但是查询只运行了很长时间(一小时一次)，而它应该只需要几秒钟。任何人都可以看到我的代码中的错误吗？

Answer 1

您需要一个 WHERE 子句将 from 表达式连接到 update 表。

大概是这样的。

UPDATE test_table
SET aw = subquery.aw_temp
FROM (SELECT SUM(COALESCE((ST_Area(ST_Intersection(a.wkb_geometry, b.wkb_geometry))/ST_Area(a.wkb_geometry))*100, 0)) AS aw_temp,a.wkb_geometry
            FROM test_table a
                LEFT OUTER JOIN filter_ancientwoodlands b ON
                    ST_Overlaps(a.wkb_geometry, b.wkb_geometry) OR ST_Within(b.wkb_geometry, a.wkb_geometry)
            GROUP BY a.ogc_fid,
                     a.poly_id,
                     a.title_no,
                     a.wkb_geometry,
                     a.distcode) AS subquery
WHERE
    subquery.wkb_geometry = test_table.wkb_geometry;