PostgreSQL创建返回多元素数组函数报subquery must return only one column错误求助

错误原因 #

• 核心报错subquery must return only one column是因为PostgreSQL的array()构造函数仅支持子查询返回单列结果，你当前子查询返回了近30个字段，无法直接构造为一维数组
• 其次函数定义的返回类型anyelement是单值多态类型，既不匹配数组类型，也无法支撑返回多列结果集
• 额外问题：你定义了入参visit anyelement但查询条件里硬编码了vi.cve_visita = 696787，入参完全没有生效，调用时传的参数不会起任何作用

解决方案1：返回多列结果集（适配你select * from 函数()的调用方式） #

绝大多数业务场景下你需要的是直接返回多列查询结果，用RETURNS TABLE声明返回结构即可：

步骤1：修改函数定义 #

注意将下面模板中的字段类型替换为你实际表对应字段的类型：

CREATE OR REPLACE FUNCTION public.buscarexamen(p_cve_visita INT) -- 直接用实际入参类型，无需多态anyelement
RETURNS TABLE (
    folio_paciente VARCHAR, -- 替换为fn_folio_paciente的实际返回类型
    cve_visita INT,
    nombre_grupo VARCHAR,
    cve_grupo INT,
    cve_visita_prueba INT,
    cve_prueba INT,
    nombre_prueba VARCHAR,
    nombre_metodo VARCHAR,
    visible_grupo BOOLEAN,
    cve_grupo_campo_formato INT,
    grupo_parametro VARCHAR,
    nombre_parametro VARCHAR,
    orden INT,
    tipo_dato VARCHAR,
    tipo_titulo INT,
    cve_seccion_padre INT,
    tiene_elementos_seccion BOOLEAN,
    observacion_parametro VARCHAR,
    cve_prueba_resultado INT,
    tipo_formato INT,
    cantidad_parametro NUMERIC,
    observacion_analista VARCHAR,
    observaciones_prueba VARCHAR,
    prueba_tiene_observacion INT,
    cve_resultado_campo INT,
    resultado VARCHAR,
    observacion_resultado VARCHAR,
    signo VARCHAR,
    decimales INT,
    cursiva NUMERIC,
    valor_referencia_min NUMERIC,
    valor_referencia_max NUMERIC,
    nombre_unidad VARCHAR,
    tiene_valores_referencia BOOLEAN
)
LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY -- 用RETURN QUERY返回查询结果集
    SELECT 
        fn_folio_paciente(vi.clave_unilabor, vi.fecha, vi.consec) AS folio_paciente,
        vi.cve_visita, gp.nombre AS nombre_grupo, gp.cve_grupo,    
        vp.cve_visita_prueba, p.cve_prueba, p.nombre AS nombre_prueba, me.nombre AS nombre_metodo,
        gcf.visible AS visible_grupo,
        cf.cve_grupo_campo_formato, gc.nombre_grupo_campo AS grupo_parametro, cf.nombre AS nombre_parametro,
        cf.orden, cf.tipo_dato, cf.solo_lectura AS tipo_titulo,
        cf.cve_seccion_padre, cf.tiene_elementos_seccion, cf.observacion AS observacion_parametro,
        pr.cve_prueba_resultado, pr.tipo_formato_prueba AS tipo_formato, pr.cantidad_resultado AS cantidad_parametro,
        pr.observaciones AS observacion_analista, pr.observaciones_prueba,
        CASE WHEN (TRIM(pr.observaciones) <> '') OR  (TRIM(pr.observaciones_prueba) <> '') THEN 1 ELSE 0 END AS prueba_tiene_observacion,
        rc.cve_resultado_campo, rc.resultado, rc.observacion AS observacion_resultado, rc.signo, rc.decimales, cf.xleft as cursiva,
        vr.valor_referencia_min, vr.valor_referencia_max, un.nombre AS nombre_unidad,
        cf.tiene_valores_referencia
    FROM visitas vi
        JOIN corporativo.unilabor_unidades uu ON (vi.cve_unilabor_unidad = uu.cve_unilabor_unidad)
        JOIN visita_pruebas vp ON (vi.cve_visita = vp.cve_visita)
        JOIN prueba_resultados pr ON (vp.cve_visita_prueba = pr.cve_visita_prueba)
        JOIN prueba_metodo pm ON (pr.cve_prueba_metodo = pm.cve_prueba_metodo)
        JOIN metodos me ON (pm.cve_metodo = me.cve_metodo)
        JOIN pruebas p ON (pr.cve_prueba = p.cve_prueba)
        JOIN grupos gh ON (p.cve_grupo = gh.cve_grupo)
        JOIN grupos gp ON (gh.cve_grupo_padre = gp.cve_grupo)
        JOIN pacientes pa ON (vi.cve_paciente = pa.cve_paciente)
        JOIN contratos co ON (vi.cve_contrato = co.cve_contrato AND co.contrato_cerrado = 0)
        JOIN resultados_campos rc ON (pr.cve_prueba_resultado = rc.cve_prueba_resultado)
        JOIN unidades un ON (rc.cve_unidad = un.cve_unidad)
        LEFT JOIN valor_referencia_resultado vr ON (rc.cve_resultado_campo = vr.cve_resultado_campo)
        JOIN campos_formatos cf ON (rc.cve_campo_formato = cf.cve_campo_formato)
        JOIN grupos_campos_formato gcf ON (cf.cve_grupo_campo_formato = gcf.cve_grupo_campo_formato)
        JOIN grupos_campos gc ON (gcf.cve_grupo_campo = gc.cve_grupo_campo)
    WHERE vi.cve_visita = p_cve_visita -- 替换为入参，去掉硬编码
        AND cf.solo_lectura = 0
    ORDER BY p.orden_prueba, pr.cve_prueba_resultado, gcf.orden_grupo, rc.orden_local, vr.orden_valor;
END;
$function$;

步骤2：调用方式保持原有习惯 #

select * from buscarexamen(696787); -- 传入你需要查询的cve_visita值即可

解决方案2：确需返回数组的修改方法 #

如果你确实需要将每行所有字段作为一个数组元素，返回一维数组，可以先将每行字段封装为行类型再转为统一格式：

CREATE OR REPLACE FUNCTION public.buscarexamen(p_cve_visita INT)
RETURNS TEXT[] -- 也可以根据实际需求改为JSON[]等其他数组类型
LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN ARRAY(
        SELECT ROW(
            fn_folio_paciente(vi.clave_unilabor, vi.fecha, vi.consec),
            vi.cve_visita, gp.nombre, gp.cve_grupo,
            -- 剩余所有查询字段按顺序列在这里
            cf.tiene_valores_referencia
        )::TEXT
        -- 原FROM、WHERE、ORDER部分和解决方案1完全一致
    );
END;
$function$;

内容的提问来源于stack exchange，提问作者Aluisco Ricardo Mastrapa