pg 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换

这个问题真TMD狗血。

做了一个函数，调试完成后运行都ok。

CREATE OR REPLACE FUNCTION "public"."fn_UpsertDeptAppParms"("pyear" int2, "pmonth" int2)
  RETURNS "pg_catalog"."bool" AS $BODY$
DECLARE 
    PYearMonth  VARCHAR;
    PFieldName  VARCHAR;
    aSQL VARCHAR;
    ARecord  RECORD;    
BEGIN
    -- Routine body goes here...
  PYearMonth := to_char(Pyear, 'fm9999') ||'-'|| to_char(Pmonth, 'fm00');
    
    aSQL := 'select * from "public"."DeptApportionParamsCollect_View" where "DAPC_YearMonth" ='||quote_nullable(trim(PYearMonth));
  FOR ARecord in (select * from "public"."DeptApportionParamsCollect_View" where "DAPC_YearMonth" = (PYearMonth)) loop  -- = quote_nullable(trim(PYearMonth))) loop
        PFieldName := ARecord."DAPC_FieldName"; 
    aSQL :=
        'INSERT INTO "public"."DeptApportionParams"(
            "DAP_Year",--年
            "DAP_Month",--月
            "HD_Type",--科室大类
            "HD_No",--科室ID
            "HD_Code",--科室代码
            "HD_Name",--科室名称
            "'|| PFieldName||'",
             "DAP_Updatetime"
            )
      values
      (
            '||Pyear||',
            '||Pmonth||', 
            '||quote_nullable(ARecord."HD_Type")||',
            '||quote_nullable(ARecord."HD_No")||',
            '||quote_nullable(ARecord."HD_Code")||',
            '||quote_nullable(ARecord."HD_Name")||',
            '||quote_nullable(ARecord."DAPC_Value")||',
            now()
            )
     ON conflict ON CONSTRAINT "pr_DAP_Key"  DO UPDATE SET 
    
        "'||PFieldName||'" = '||quote_nullable(ARecord."DAPC_Value")||',
        "DAP_Updatetime" = NOW()
    ';
    EXECUTE aSQL;
  END loop;

    RETURN TRUE;
EXCEPTION 
    WHEN OTHERS THEN
        RETURN FALSE;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

 

 

可是调用时出现错误：

SELECT "public"."fn_UpsertDeptAppParms"(2022, 2)

错误:  函数 public.fn_UpsertDeptAppParms(integer, integer) 不存在
LINE 1: EXPLAIN SELECT "public"."fn_UpsertDeptAppParms"(2022, 2)
                       ^
HINT:  没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.

网上找了一大堆，也没看懂弄明白，照抄一通还是不行。

折腾几天了。突然想起在upsert中，字符串也可以insert到integer类型字段。于是把参数修改成：

SELECT "public"."fn_UpsertDeptAppParms"('2022', '2')

居然成功了：
EXPLAIN SELECT "public"."fn_UpsertDeptAppParms"('2022', '2')
> OK
> 查询时间: 0s


pg具有自动转型的功能。难以想到。但为什么数字就不行呢？你知道请告诉我。

https://www.cnblogs.com/usegear/p/16363994.html