使用ADO.NET/Dapper从PostgreSQL视图取数时遇“Invalid Return Type”错误

场景说明 #

尝试从名为vwindicador_separacao_pedido_inicio_separacao_fisica_resumo的PostgreSQL视图查询数据，预期返回nr_ano、nr_mes、pc_aferido三个字段，用于计算指定id_grupocliente的指标。

所用C# Dapper代码 #

string query =
    @"SELECT * FROM vwindicador_separacao_pedido_inicio_separacao_fisica_resumo WHERE id_grupocliente = @idGrupoCliente";

return (await conn.QueryAsync<IndicadorPortal>(query, new { idGrupoCliente })).ToList();

实体类定义 #

public class IndicadorPortal
{
    public int nr_ano { get; set; }
    public int nr_mes { get; set; }
    public decimal pc_aferido { get; set; }
}

错误信息 #

Npgsql.PostgresException (0x80004005): 42601: invalid return type
DETAIL: Query-specified return tuple has 17 columns but crosstab returns 19.

视图创建SQL #

DROP VIEW IF EXISTS vwindicador_separacao_pedido_inicio_separacao_fisica_resumo;
CREATE VIEW vwindicador_separacao_pedido_inicio_separacao_fisica_resumo AS
SELECT
    EXTRACT(YEAR FROM dt_inclusao)                     nr_ano,
    EXTRACT(Month from dt_inclusao)                    nr_mes,
    sum(case
            when coalesce(iso.data_pedido_separado, now() ) > dt_prazofinalproducao then 0.00
            else 1.00 end::float) / count(*)  pc_aferido,
    a.id_grupocliente
FROM public.vwacompanhamento a
         INNER JOIN vwindicadorsemocorrencias iso ON a.id_sem = iso.id_sem
group by EXTRACT(YEAR FROM dt_inclusao),
         EXTRACT(Month from dt_inclusao)
       , a.id_grupocliente

已尝试的无效方案 #

• 使用dynamic类型和IDictionary<string, object>作为返回类型
• 改用ADO.NET测试，错误依旧

解决方法 #

1. 排查依赖视图的crosstab问题 #

错误提示提到crosstab，说明当前视图依赖的基础视图（vwacompanhamento或vwindicadorsemocorrencias）中使用了crosstab函数，且该函数的返回列数与定义不符：

• 查看这两个基础视图的创建SQL，确认crosstab调用是否符合规则：crosstab要求查询返回的列数必须与定义的返回类型列数严格匹配，比如crosstab(text, text)的第二个参数定义的列数要和实际结果列数一致
• 检查是否有新增/删除列后未同步更新crosstab的返回类型定义

2. 显式指定查询列，避免SELECT * #

即使修复基础视图后，也建议放弃SELECT *，明确指定需要的列，从根源减少列数不匹配风险：

string query =
    @"SELECT nr_ano, nr_mes, pc_aferido 
      FROM vwindicador_separacao_pedido_inicio_separacao_fisica_resumo 
      WHERE id_grupocliente = @idGrupoCliente";

3. 修复数据类型不匹配 #

当前视图中nr_ano和nr_mes是EXTRACT返回的double precision类型，但实体类定义为int，可能引发映射异常，建议在视图中显式转换类型：

SELECT
    EXTRACT(YEAR FROM dt_inclusao)::int                     nr_ano,
    EXTRACT(Month from dt_inclusao)::int                    nr_mes,
    (sum(case
            when coalesce(iso.data_pedido_separado, now() ) > dt_prazofinalproducao then 0.00
            else 1.00 end::float) / count(*))::decimal  pc_aferido,
    a.id_grupocliente

4. 重新创建所有依赖视图 #

如果基础视图的crosstab存在错误，先修复基础视图，再重新创建当前视图：

-- 先修复vwacompanhamento或vwindicadorsemocorrencias中的crosstab问题
-- 然后重新创建当前视图
DROP VIEW IF EXISTS vwindicador_separacao_pedido_inicio_separacao_fisica_resumo;
CREATE VIEW vwindicador_separacao_pedido_inicio_separacao_fisica_resumo AS
-- 写入修复后的SELECT语句

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