postgresql - 如何解决错误：返回列与预期列不匹配？

Question

我在 PostgreSQL 中编写函数。我正在尝试通过在函数中传递参数来从数据库中获取数据。但是我收到错误“错误：查询结构与函数结果类型不匹配”。在这里，我创建了一个函数，在该函数中我使用联合来获取唯一值。首先选择 stat。我正在设置以下代码中使用的帐户总额的值。

功能：

        CREATE OR REPLACE FUNCTION GetDeptListForViewModifyJointUsePercentages ( p_nInstID numeric,p_nDeptID numeric)  
    RETURNS Table(
    res_ndept_id numeric,
    res_salaryPercent character varying,
    res_nclient_cpc_mapping_id numeric, 
    res_CPCCODE character varying, 
    res_sdept_name character varying, 
    res_sclient_dept_id character varying, 
    res_sAlternateJointUsePercentage character varying
    )
    AS  $$  
    declare v_AccountTotal numeric(18,2);
    BEGIN
    RETURN QUERY 
    select SUM(CAST (coalesce(eam.npayroll_amt, null) AS numeric(18,2))) as AccountTotal
            FROM Account acct
                INNER JOIN employeeaccountmapping eam 
                    ON eam.nacct_id = acct.naccount_id
                    AND acct.ninst_id =p_nInstID
              where acct.ndept_id =p_nDeptID ;

    SELECT  * 
    FROM 
        (select dep.ndept_id ,( CASE
                        WHEN v_AccountTotal =0 THEN 0
                        ELSE Round(SUM(CAST(coalesce(eam.npayroll_amt, null) AS numeric(18,2)) * cac.npercentage_assigned/100)  / v_AccountTotal *100,null)  
                        END     
                    ) as salaryPercent
        ,cac.nclient_cpc_mapping_id,client.sclient_cpc AS CPCCODE,
        dep.sdept_name,dep.sclient_dept_id,'NO' ASsAlternateJointUsePercentage

        FROM account 
        INNER JOIN employeeaccountmapping eam 
        ON eam.nacct_id = account .naccount_id AND account .ninst_id=p_nInstID 
        INNER JOIN accountcpcmapping acm 
        ON acm.naccount_cpc_mapping_id = account.naccount_cpc_mapping_id 
        INNER JOIN cpcaccountcpcmapping as cac
        ON cac.naccount_cpc_mapping_id=acm.naccount_cpc_mapping_id
        INNER JOIN clientcostpoolcodes as client
        ON client.nclient_cpc_mapping_id=cac.nclient_cpc_mapping_id
        INNER JOIN mastercostpoolcodes 
        ON mastercostpoolcodes .nmaster_cpc_id= client.nmaster_cpc_id
        INNER JOIN department as dep
        ON account.ndept_id=dep.ndept_id and dep.balternate_jointuse_percentage=FALSE
        where account.ndept_id =p_nDeptID 
        Group by dep.ndept_id,cac.nclient_cpc_mapping_id,client.sclient_cpc
        ,dep.sdept_name,dep.sclient_dept_id, dep.balternate_jointuse_percentage
        UNION
        SELECT     Department_1.ndept_id, a_1.SumByCPC, clientcostpoolcodes.nclient_cpc_mapping_id, clientcostpoolcodes .sclient_cpc, Department_1.sdept_name, Department_1.sclient_dept_id , 'YES' AS sAlternateJointUsePercentage 

        FROM   department AS Department_1 LEFT OUTER JOIN
               mastercostpoolcodes RIGHT OUTER JOIN
               clientcostpoolcodes RIGHT OUTER JOIN
                              (SELECT   JointUseStatistics_1.nccp_code, SUM(JointUseStatistics_1.npercent) /
                                                           (SELECT  SUM(jointusestatistics.npercent) AS SumByCPC
                                                           FROM          jointusestatistics INNER JOIN
                                                                                    roomdepartmentmapping ON jointusestatistics.nroom_allocation_id = roomdepartmentmapping .nroom_allocation_id
                                                             WHERE      (roomdepartmentmapping .ndept_id = Room_1.ndept_id)) * 100 AS SumByCPC, 
                                                          Room_1.ndept_id
                                  FROM jointusestatistics JointUseStatistics_1 INNER JOIN roomdepartmentmapping  AS Room_1 ON JointUseStatistics_1.nroom_allocation_id = Room_1.nroom_allocation_id
  GROUP BY JointUseStatistics_1.nccp_code, JointUseStatistics_1.npercent, Room_1.ndept_id) AS a_1 ON 
          clientcostpoolcodes .nclient_cpc_mapping_id = a_1.nccp_code ON mastercostpoolcodes .nmaster_cpc_id = clientcostpoolcodes .nmaster_cpc_id ON 
          Department_1.ndept_id = a_1.ndept_id                      
        WHERE     (Department_1.balternate_jointuse_percentage = 'TRUE')
                AND (Department_1.ninst_id= p_nInstID)
    )AS My 

    where (ndept_id= p_nDeptID )
    ORDER BY My.sdept_name,My.CPCCODE ;
    END;    
    $$ LANGUAGE plpgsql;

标签： postgresql

Answer 1

在 Postgresql 中，函数返回单个值。但在您的示例中，您试图根据行数和列数返回多个输出。

您也可以使用 astored procedure并将输出保存在 a 中temporary table。稍后使用该表供您使用。