PostgreSQL 9.4转v13：动态返回表函数迁移报错修复咨询

问题根源 #

PostgreSQL 10及以后版本增强了多态类型（如anyelement、record）的类型推导规则，9.4中允许的模糊类型定义在高版本中会触发类型解析错误：

• 用record参数时，高版本需要提前知晓具体记录类型，否则报ERROR 42704
• 用anyelement时，调用时必须提供足够的类型上下文让PostgreSQL推导具体类型，否则报ERROR 42804

方案1：保留动态SQL，添加表名参数辅助类型推导 #

这种方案改动最小，适配VB.NET调用场景，核心是通过传入表名让函数明确操作的表类型，避免类型推导失败。

函数示例代码 #

CREATE OR REPLACE FUNCTION spatial_query(
    p_table_name text,
    p_column_name text,
    p_geom geometry,
    p_operator text DEFAULT '&&'
) RETURNS SETOF record AS $$
DECLARE
    v_sql text;
BEGIN
    -- 构造动态SQL，用%I占位符防止SQL注入
    v_sql := format(
        'SELECT * FROM %I WHERE %I %L $1',
        p_table_name,
        p_column_name,
        p_operator
    );
    -- 执行动态SQL并指定返回类型
    RETURN QUERY EXECUTE v_sql USING p_geom
    SETOF pg_catalog.regclass(p_table_name);
END;
$$ LANGUAGE plpgsql;

VB.NET调用示例 #

调用时直接传入目标表名，结果按对应表的列结构解析即可：

Dim cmd As New NpgsqlCommand("SELECT * FROM spatial_query('mytable', 'geom_col', ST_GeomFromText('POINT(116 39)'))", conn)
cmd.CommandType = CommandType.Text
' 后续按mytable的列定义读取DataReader结果

方案2：改用anyelement并强制传递类型上下文 #

如果坚持使用多态参数，需在调用时显式传递类型提示，避免类型推导失败。

修正后的函数代码 #

CREATE OR REPLACE FUNCTION spatial_query(
    p_row anyelement,
    p_column_name text,
    p_geom geometry,
    p_operator text DEFAULT '&&'
) RETURNS SETOF anyelement AS $$
DECLARE
    v_sql text;
    v_table_name text := pg_typeof(p_row)::text;
BEGIN
    v_sql := format(
        'SELECT * FROM %I WHERE %I %L $1',
        v_table_name,
        p_column_name,
        p_operator
    );
    RETURN QUERY EXECUTE v_sql USING p_geom;
END;
$$ LANGUAGE plpgsql;

VB.NET调用示例 #

调用时必须传递目标表的空行作为类型上下文：

Dim cmd As New NpgsqlCommand("SELECT * FROM spatial_query(NULL::mytable, 'geom_col', ST_GeomFromText('POINT(116 39)'))", conn)

关键注意事项 #

• 必须使用format()函数的%I占位符处理表名、列名，防止SQL注入
• 确保目标列是geometry/geography类型，匹配空间操作符的要求
• VB.NET需使用适配PostgreSQL 13的Npgsql驱动版本，避免类型解析兼容问题

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