function - 没有函数匹配给定的名称和参数类型

Question

我的职能是:

 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 **********

Answer 1

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

字符串文字或 string constant ('123') 没有立即输入。在明确分配或转换之前，它保持类型“未知”。

但是，数字文字或 numeric constant 立即输入。 The manual:

A numeric constant that contains neither a decimal point nor anexponent is initially presumed to be type integer if its valuefits in type integer (32 bits); otherwise it is presumed to be typebigint if its value fits in type bigint (64 bits); otherwise it istaken to be type numeric. Constants that contain decimal points and/orexponents are always initially presumed to be type numeric.

另见:

• PostgreSQL ERROR: function to_tsvector(character varying, unknown) does not exist

解决方案

为 smallint 参数添加显式转换或传递带引号(无类型)的文字。

演示

CREATE OR REPLACE FUNCTION f_typetest(<b>smallint</b>)
  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 here
<子>旧sqlfiddle.