如何在PostgreSQL动态查询中集成FOR循环更新相交多边形点计数

问题原因说明 #

• 第一版报错原因：EXECUTE 只能执行标准SQL语句，FOR ... LOOP 属于PL/pgSQL的过程控制语法，不属于SQL语法范围，因此直接写在EXECUTE的执行字符串内会触发语法错误。
• 第二版空值报错原因：你创建触发器时传入了4个参数，而PL/pgSQL触发器的TG_ARGV数组下标从0开始计数，你读取点表名时用了TG_ARGV[4]，读取到的是空值，format函数的%I（标识符占位符）不允许传入空值，因此触发报错。
• 动态SQL上下文隔离问题：动态SQL执行时是独立上下文，无法直接访问PL/pgSQL函数内定义的变量，因此UPDATE语句中的tmprow变量不能直接写在动态SQL字符串内，需要把对应值作为参数传入format函数。

完整可用实现代码 #

CREATE OR REPLACE
FUNCTION update_points_in_polygon()
RETURNS trigger LANGUAGE plpgsql AS $function$
DECLARE 
  tables_schema varchar;
  polygon_table_name varchar;
  polygon_point_count_column_name varchar;
  point_table_name varchar;
  tmprow record;
BEGIN
  tables_schema = TG_ARGV[0];
  polygon_table_name = TG_ARGV[1];
  polygon_point_count_column_name = TG_ARGV[2];
  point_table_name = TG_ARGV[3]; -- 修正下标，从4改为3

  FOR tmprow IN 
    EXECUTE format('SELECT c.objectid, count(a.*) point_count 
      FROM %I.%I a
      JOIN %I.%I c
      ON st_intersects(a.wkb_geometry, c.wkb_geometry)            
      WHERE st_intersects(c.wkb_geometry, $1)
      GROUP BY c.objectid',
      tables_schema, point_table_name, tables_schema, polygon_table_name
    ) USING NEW.wkb_geometry -- 用USING传参数避免geometry转文本的格式问题
  LOOP
    EXECUTE format('UPDATE %I.%I c1
      SET %I = %s
      WHERE c1.objectid = %s ', 
      tables_schema, polygon_table_name, polygon_point_count_column_name, tmprow.point_count, tmprow.objectid);
  END LOOP;
  RETURN NEW;
END;
$function$;

注：以上代码优化了几何参数传递逻辑，用USING子句直接传递NEW.wkb_geometry，避免了几何转文本再转回来的性能损耗和潜在格式错误。

可选优化（无需循环，单条SQL完成更新） #

可以把查询和更新合并为单条动态SQL，避免循环开销，性能更好：

CREATE OR REPLACE
FUNCTION update_points_in_polygon()
RETURNS trigger LANGUAGE plpgsql AS $function$
DECLARE 
  tables_schema varchar;
  polygon_table_name varchar;
  polygon_point_count_column_name varchar;
  point_table_name varchar;
BEGIN
  tables_schema = TG_ARGV[0];
  polygon_table_name = TG_ARGV[1];
  polygon_point_count_column_name = TG_ARGV[2];
  point_table_name = TG_ARGV[3];

  EXECUTE format('
    UPDATE %I.%I c
    SET %I = (
      SELECT count(*) 
      FROM %I.%I a 
      WHERE st_intersects(a.wkb_geometry, c.wkb_geometry)
    )
    WHERE st_intersects(c.wkb_geometry, $1)
  ', tables_schema, polygon_table_name, polygon_point_count_column_name, tables_schema, point_table_name)
  USING NEW.wkb_geometry;

  RETURN NEW;
END;
$function$;

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