PostgreSQL函数中高效检查表的Geometry类型与SRID的方法

我来分享几个优化思路，既能简化你的代码，还能增强健壮性，同时覆盖你提到的SRID不匹配检查需求：

1. 封装通用检查逻辑，消除代码重复 #

你的原代码两次执行几乎完全相同的查询逻辑，只是目标表不同。可以把几何类型、SRID的检查逻辑封装成一个辅助函数，避免重复编写相似代码，后续维护也更方便：

CREATE OR REPLACE FUNCTION get_geom_table_metadata(table_name text, geom_col text DEFAULT 'geom')
RETURNS TABLE(
    geom_type text,
    srid integer,
    has_invalid_type boolean,
    has_multiple_types boolean
) AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT
            -- 获取表中几何类型（取第一条即可）
            ST_GeometryType(%I) AS geom_type,
            ST_SRID(%I) AS srid,
            -- 检查是否存在非ST_Polygon的几何
            EXISTS(SELECT 1 FROM %s WHERE ST_GeometryType(%I) != ''ST_Polygon'') AS has_invalid_type,
            -- 检查是否存在多种几何类型
            (COUNT(DISTINCT ST_GeometryType(%I)) > 1) AS has_multiple_types
         FROM %s
         LIMIT 1',
        geom_col, geom_col, table_name, geom_col, geom_col, table_name
    );
END;
$$ LANGUAGE plpgsql;

然后在你的主函数里调用这个辅助函数即可：

DECLARE
    master_meta record;
    ref_meta record;
BEGIN
    -- 检查主表元数据
    SELECT * INTO master_meta FROM get_geom_table_metadata(master_table);
    IF master_meta.has_multiple_types THEN
        RAISE EXCEPTION 'Master table contains multiple distinct geometry types';
    END IF;
    IF master_meta.has_invalid_type THEN
        RAISE EXCEPTION 'Master table geometries must be type ST_Polygon';
    END IF;

    -- 检查参考表元数据
    SELECT * INTO ref_meta FROM get_geom_table_metadata(ref_table);
    IF ref_meta.has_multiple_types THEN
        RAISE EXCEPTION 'Reference table contains multiple distinct geometry types';
    END IF;
    IF ref_meta.has_invalid_type THEN
        RAISE EXCEPTION 'Reference table geometries must be type ST_Polygon';
    END IF;

    -- 新增：检查SRID匹配
    IF master_meta.srid != ref_meta.srid THEN
        RAISE EXCEPTION 'SRID mismatch: master table uses %, reference table uses %', master_meta.srid, ref_meta.srid;
    END IF;
END;

2. 增强健壮性，处理边界情况 #

原代码用SELECT DISTINCT ST_GeometryType(geom)如果遇到表中存在多种几何类型时，会返回多行，直接INTO会抛出"more than one row returned"的模糊错误。上面的优化方案中，我们新增了has_multiple_types和has_invalid_type的检查，能给出更明确的错误提示，帮助快速定位问题。

3. 可选：单次查询完成双表检查（无需辅助函数） #

如果你不想创建辅助函数，也可以通过构造联合查询，一次执行完成两个表的检查，减少EXECUTE的调用次数：

DECLARE
    master_geom_type text;
    master_srid integer;
    ref_geom_type text;
    ref_srid integer;
    master_has_multi boolean;
    ref_has_multi boolean;
BEGIN
    EXECUTE format(
        'SELECT
            m.geom_type, m.srid, m.has_multi,
            r.geom_type, r.srid, r.has_multi
         FROM (
             SELECT
                 ST_GeometryType(geom) AS geom_type,
                 ST_SRID(geom) AS srid,
                 (COUNT(DISTINCT ST_GeometryType(geom)) > 1) AS has_multi
             FROM %s
             LIMIT 1
         ) m,
         (
             SELECT
                 ST_GeometryType(geom) AS geom_type,
                 ST_SRID(geom) AS srid,
                 (COUNT(DISTINCT ST_GeometryType(geom)) > 1) AS has_multi
             FROM %s
             LIMIT 1
         ) r',
        master_table, ref_table
    ) INTO master_geom_type, master_srid, master_has_multi, ref_geom_type, ref_srid, ref_has_multi;

    -- 主表类型检查
    IF master_has_multi OR master_geom_type != 'ST_Polygon' THEN
        RAISE EXCEPTION 'Master table must contain only ST_Polygon geometries (found invalid/multiple types)';
    END IF;

    -- 参考表类型检查
    IF ref_has_multi OR ref_geom_type != 'ST_Polygon' THEN
        RAISE EXCEPTION 'Reference table must contain only ST_Polygon geometries (found invalid/multiple types)';
    END IF;

    -- SRID匹配检查
    IF master_srid != ref_srid THEN
        RAISE EXCEPTION 'SRID mismatch: master table uses %, reference table uses %', master_srid, ref_srid;
    END IF;
END;

这些优化方案的核心目标是：减少重复代码、提升错误提示的清晰度、覆盖你需求中的SRID匹配检查，同时处理原代码未考虑的多几何类型场景。

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