没有函数与给定名称和参数类型匹配

我的职能是：

 CREATE OR REPLACE FUNCTION FnUpdateSalegtab09
(
 iacyrid Integer,iRepId Integer,iDrId Integer,ivrid Integer,imode smallint,itrno 
varchar,itrdate timestamp,iacid Integer,ivrno varchar,iSuppId Integer,icustname 
varchar,inetamt money,idisrate real,idisamt money,iRoundOff real,ijrmid integer,iuserid 
integer,iuserdtm timestamp,iVSNo integer,iRecdAmt money,icstrate real,icstsaleamt 
money,icstamt money,itdrate real,itdamt money,icdrate real,icdamt money,iCessRate 
real,iCessAmt money,iodesc1 varchar,ioamt1 money,iCashCredit boolean,iOrderNo 
varchar,iOrderDate timestamp,iCustAdd2 varchar,iRemarks varchar,iWhoRetSl boolean,iPatName 
varchar,iDrName varchar,iFormId integer,iSalesMan varchar,iCFMode smallint,iPatId 
integer,iStkPtId integer,iDisType smallint,iBranchID integer
)
RETURNS void AS
'BEGIN 
INSERT INTO gtab09 
(
acyrid, RepId, DrId, vrid, mode, trno, trdate, acid, vrno, SuppId, custname, netamt,
disrate, disamt, RoundOff, jrmid, userid, userdtm, VSNo, RecdAmt, cstrate, cstsaleamt,
cstamt, tdrate, tdamt, cdrate, cdamt, CessRate, CessAmt, odesc1, oamt1, CashCredit, 
OrderNo, OrderDate, CustAdd2, Remarks, WhoRetSl, PatName, DrName, FormId, SalesMan, 
CFMode,PatId,StkPtId,DisType,BranchID
)
values 
(   iacyrid,iRepId,iDrId,ivrid,imode,itrno,itrdate,iacid,ivrno,iSuppId,icustname,inetamt,idisra
te,idisamt,iRoundOff,ijrmid,iuserid,iuserdtm,iVSNo,iRecdAmt,icstrate,icstsaleamt,icstamt,it
drate,itdamt,icdrate,icdamt,iCessRate,iCessAmt,iodesc1,ioamt1,iCashCredit,iOrderNo,iOrderDa
te,iCustAdd2,iRemarks,iWhoRetSl,iPatName,iDrName,iFormId,iSalesMan,iCFMode,iPatId,iStkPtId,
iDisType,iBranchID);
END;'
LANGUAGE plpgsql VOLATILE
COST 100;

我曾经这样称呼它：

select FnUpdateSalegtab09 (4, 1, 0, 12, 1, '9'::varchar,'2014-07-15'::timestamp, 4048, '9'::varchar, 4048, 'MYCUSTOMER'::varchar, 12::money, 0, 0::money, 0.32, 185, 0, '2014-07-15 11:24:12 AM'::timestamp, 0, 0::money, 0, 0::money, 0::money, 0, 0::money, 0, 0::money, 0, 0::money, ''::varchar, 0::money, False, ''::varchar, '2014-07-15'::timestamp, ''::varchar, ''::varchar, False, ''::varchar, ''::varchar, 1, ''::varchar, 1,0,1,0,42)

错误是：

ERROR:  function fnupdatesalegtab09(integer, integer, integer, integer, integer, unknown, unknown, integer, unknown, integer, unknown, integer, integer, integer, numeric, integer, integer, unknown, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, unknown, integer, boolean, unknown, unknown, unknown, unknown, boolean, unknown, unknown, integer, unknown, integer, integer, integer, integer, integer) does not exist  
LINE 1: select FnUpdateSalegtab09 (4, 1, 0, 12, 1, '9','2014-07-15',...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.  
********** Error **********

你的函数有几个smallint参数。
但在调用中，您使用的数字文字被假定为类型integer.

字符串文字或字符串常量 https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS ('123') 不会立即输入。在显式分配或强制转换之前，它保持类型“未知”。

然而，数字文字或数值常量 https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-NUMERIC立即输入。手册： https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-NUMERIC

既不包含小数点也不包含小数点的数值常量 指数是最初推测为类型integer如果它的值 适合类型integer（32 位）；否则它被认为是类型bigint如果它的值符合类型bigint（64 位）；否则就是 被视为类型numeric。包含小数点和/或的常量 指数最初总是被假定为类型numeric.

另请参阅：

• PostgreSQL 错误：函数 to_tsvector（字符变化，未知）不存在 https://stackoverflow.com/questions/14523624/postgresql-error-function-to-tsvectorcharacter-varying-unknown-does-not-exis/14524599#14524599

Solution

添加显式强制转换smallint参数或传递带引号的（无类型）文字。

Demo

CREATE OR REPLACE FUNCTION f_typetest(smallint)
  RETURNS bool AS 'SELECT TRUE' LANGUAGE sql;

错误的调用：

SELECT * FROM f_typetest(1);

正确调用：

SELECT * FROM f_typetest('1');
SELECT * FROM f_typetest(smallint '1');
SELECT * FROM f_typetest(1::int2);
SELECT * FROM f_typetest('1'::int2);

db<>fiddle
_{Old sqlfiddle. http://sqlfiddle.com/#!17/862dc/1}