如何创建可调用其他聚合函数、支持异常处理的PostgreSQL聚合函数？

当然可以实现这样的自定义聚合函数！你的需求核心是按优先级尝试合并几何图形，失败则降级处理，最终返回布尔值，咱们用PL/pgSQL结合PostGIS来落地这个逻辑。

核心思路 #

我们需要两个部分配合实现：

1. 一个处理降级逻辑的PL/pgSQL函数，负责按顺序执行ST_COLLECT→ST_UNION→逐个检查的验证流程，并捕获所有异常。
2. 一个自定义聚合函数，将查询返回的几何集合收集成数组，再传递给上面的处理函数完成最终验证。

代码实现 #

第一步：确保PostGIS已启用 #

先确认PostGIS扩展安装完成，因为我们要用到它的几何处理函数：

CREATE EXTENSION IF NOT EXISTS postgis;

第二步：编写降级逻辑处理函数 #

这个函数接收几何数组，严格按照你的伪代码逻辑执行验证，带完整的异常捕获：

CREATE OR REPLACE FUNCTION check_fixed_containment(geometries geometry[])
RETURNS boolean AS $$
DECLARE
    -- 定义你需要验证的固定目标几何图形
    target_geom geometry := ST_GeomFromText('POLYGON((0 4096,0 0,4096 0,4096 4096,0 4096))', 3857);
    merged_geom geometry;
    individual_result boolean;
BEGIN
    -- 第一优先级：用ST_COLLECT合并后验证
    BEGIN
        merged_geom := ST_COLLECT(geometries);
        RETURN ST_WITHIN(target_geom, merged_geom);
    EXCEPTION
        WHEN OTHERS THEN
            -- 第二优先级：用ST_UNION合并后验证
            BEGIN
                merged_geom := ST_UNION(geometries);
                RETURN ST_WITHIN(target_geom, merged_geom);
            EXCEPTION
                WHEN OTHERS THEN
                    -- 第三优先级：逐个检查每个几何图形
                    SELECT MAX(ST_WITHIN(target_geom, geom)) INTO individual_result
                    FROM unnest(geometries) AS geom;
                    -- 空集合或所有检查失败时返回FALSE
                    RETURN COALESCE(individual_result, FALSE);
            END;
    END;
-- 所有步骤都失败时返回FALSE
EXCEPTION
    WHEN OTHERS THEN
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE;

第三步：创建自定义聚合函数 #

这个聚合函数会把查询结果里的所有几何图形收集成数组，最后调用上面的处理函数完成验证：

CREATE AGGREGATE test_is_empty(geometry) (
    SFUNC = array_append,  -- 把每个几何图形添加到数组状态中
    STYPE = geometry[],    -- 聚合状态的类型是几何数组
    INITCOND = '{}',       -- 初始状态为空数组
    FINALFUNC = check_fixed_containment  -- 最终调用处理函数
);

使用示例 #

现在你可以直接用这个聚合函数替代原来的查询：

SELECT test_is_empty(geometry) AS IsEmpty 
FROM (select geometry from your_target_table) AS src;

关键细节说明 #

• 异常处理：用WHEN OTHERS捕获所有可能的合并异常（比如无效几何、拓扑错误等），确保流程能顺利降级。如果需要针对特定异常做处理，可以替换成具体的SQLSTATE代码。
• 逐个检查逻辑：用MAX(ST_WITHIN(...))判断是否存在至少一个几何包含目标图形——只要有一个返回TRUE，MAX结果就是TRUE，否则为FALSE；如果输入集合为空，MAX返回NULL，用COALESCE转成FALSE。
• 性能优化：函数标记为STABLE，告诉PostgreSQL函数结果只依赖输入参数，便于查询优化。

灵活扩展（可选） #

如果需要动态传入目标几何（而不是固定值），可以修改处理函数和聚合函数：

-- 修改后的处理函数，支持动态目标几何
CREATE OR REPLACE FUNCTION check_dynamic_containment(target_geom geometry, geometries geometry[])
RETURNS boolean AS $$
DECLARE
    merged_geom geometry;
    individual_result boolean;
BEGIN
    -- 逻辑和之前一致，只是目标几何从参数传入
    BEGIN
        merged_geom := ST_COLLECT(geometries);
        RETURN ST_WITHIN(target_geom, merged_geom);
    EXCEPTION
        WHEN OTHERS THEN
            BEGIN
                merged_geom := ST_UNION(geometries);
                RETURN ST_WITHIN(target_geom, merged_geom);
            EXCEPTION
                WHEN OTHERS THEN
                    SELECT MAX(ST_WITHIN(target_geom, geom)) INTO individual_result
                    FROM unnest(geometries) AS geom;
                    RETURN COALESCE(individual_result, FALSE);
            END;
    END;
EXCEPTION
    WHEN OTHERS THEN
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE;

-- 对应的聚合函数，接受两个参数：目标几何、待检查几何
CREATE AGGREGATE test_is_empty(geometry, geometry) (
    SFUNC = array_append,
    STYPE = geometry[],
    INITCOND = '{}',
    FINALFUNC = check_dynamic_containment
);

使用时传入目标几何：

SELECT test_is_empty(
    ST_GeomFromText('POLYGON((0 4096,0 0,4096 0,4096 4096,0 4096))', 3857),
    geometry
) AS IsEmpty 
FROM (select geometry from your_target_table) AS src;

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