PostgreSQL 13.9动态SQL报错：null无法作为SQL标识符格式化

运行以下PL/pgSQL脚本（?位置传入GeoJSON数据）：

DO $$
DECLARE
    lib_table_name text;
    lib_table_id text;
    geom geometry;
BEGIN
    SELECT
        point_node_libs.lib_type,
        point_node_libs.lib_type_id,
        nodes.geometry
        INTO lib_table_name, lib_table_id, geom
        FROM point_nodes AS nodes
        LEFT JOIN point_node_libs
        ON point_node_libs.id = nodes.node_lib_id
        WHERE ST_DWithin(geom, ST_GeomFromGeoJSON(?), 0);

    EXECUTE format('
    SELECT
        id,
        geometry,
        metadata,
        draft,
        node_lib_id AS nodeLibId,
        created_at AS createdAt,
        updated_at AS updatedAt,
        point_node_libs.*,
        lib_table.*
        FROM point_nodes AS points
        LEFT JOIN point_node_libs
        ON point_node_libs.id = points.node_lib_id
        LEFT JOIN (
            SELECT *
            FROM %I
            WHERE id=%s
        ) lib_table
        WHERE $1;', lib_table_name, lib_table_id) USING (ST_DWithin(geom, ST_GeomFromGeoJSON(?), 0));
END $$;

执行时报错：

[22004] ERROR: null values cannot be formatted as an SQL identifier
Where: PL/pgSQL function inline_code_block line 17 at EXECUTE

使用数据库版本为PostgreSQL 13.9，需排查错误原因及解决方法。

1. SQL标识符格式化不允许NULL：format函数的%I用于格式化表名、列名这类SQL标识符，不接受NULL值。脚本中用LEFT JOIN point_node_libs，当point_nodes里的node_lib_id为空或关联不到对应记录时，point_node_libs.lib_type会返回NULL，导致lib_table_name为NULL，触发报错。
2. 初始查询逻辑错误：第一个SELECT的WHERE条件使用了未初始化的geom变量（此时geom是DECLARE声明的空值），这个查询大概率返回空结果，最终lib_table_name和lib_table_id都会变成NULL。

1. 修复初始SELECT的WHERE条件 #

把第一个查询WHERE条件里的geom替换为nodes.geometry——geom是查询后才赋值的变量，初始为空，无法用于空间判断：

SELECT
    point_node_libs.lib_type,
    point_node_libs.lib_type_id,
    nodes.geometry
    INTO lib_table_name, lib_table_id, geom
    FROM point_nodes AS nodes
    LEFT JOIN point_node_libs
    ON point_node_libs.id = nodes.node_lib_id
    WHERE ST_DWithin(nodes.geometry, ST_GeomFromGeoJSON(?), 0);

2. 处理NULL值，避免格式化失败 #

根据业务需求选择对应方案：

• 仅处理能关联到point_node_libs的记录：
  将LEFT JOIN改为INNER JOIN，确保lib_table_name和lib_table_id不会为NULL：

  SELECT
      point_node_libs.lib_type,
      point_node_libs.lib_type_id,
      nodes.geometry
      INTO lib_table_name, lib_table_id, geom
      FROM point_nodes AS nodes
      INNER JOIN point_node_libs
      ON point_node_libs.id = nodes.node_lib_id
      WHERE ST_DWithin(nodes.geometry, ST_GeomFromGeoJSON(?), 0);
  

  同时在EXECUTE前增加非空判断，防止意外：

  IF lib_table_name IS NULL OR lib_table_id IS NULL THEN
      RAISE NOTICE 'No valid library table found for the given GeoJSON';
      RETURN;
  END IF;
  

• 允许关联不到point_node_libs的记录：
  保留LEFT JOIN，但在格式化时为lib_table_name设置默认逻辑，比如跳过关联子查询：

  EXECUTE format('
  SELECT
      id,
      geometry,
      metadata,
      draft,
      node_lib_id AS nodeLibId,
      created_at AS createdAt,
      updated_at AS updatedAt,
      point_node_libs.*,
      lib_table.*
      FROM point_nodes AS points
      LEFT JOIN point_node_libs
      ON point_node_libs.id = points.node_lib_id
      %s
      WHERE ST_DWithin(points.geometry, ST_GeomFromGeoJSON(?), 0);', 
      CASE WHEN lib_table_name IS NOT NULL AND lib_table_id IS NOT NULL THEN
          format('LEFT JOIN (SELECT * FROM %I WHERE id=%L) lib_table', lib_table_name, lib_table_id)
      ELSE
          ''
      END);
  

3. 规避SQL注入风险 #

原脚本用%s格式化lib_table_id存在SQL注入风险，建议改为%L（格式化字符串常量）或用USING传递参数：

EXECUTE format('
SELECT ...
LEFT JOIN (
    SELECT *
    FROM %I
    WHERE id = $2
) lib_table
WHERE ST_DWithin(points.geometry, ST_GeomFromGeoJSON($1), 0);', lib_table_name) 
USING (?, lib_table_id);

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