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

No function matches the given name and argument types

提问by Vivek S.

My function is:

我的功能是：

 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;

And I used to call this like:

我曾经这样称呼它：

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)

The error is:

错误是：

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

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

回答by Erwin Brandstetter

Your function has a couple of smallintparameters.
But in the call, you are using numeric literals that are presumed to be type integer.

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

A string literal or string constant('123') is not typed immediately. It remains type "unknown" until assigned or cast explicitly.

字符串文字或字符串常量( '123') 不会立即键入。在显式分配或强制转换之前，它保持类型“未知”。

However, a numeric literal or numeric constantis typed immediately. Per documentation:

但是，立即键入数字文字或数字常量。根据文档：

A numeric constant that contains neither a decimal point nor an exponent is initially presumed to be type integerif its value fits in type integer(32 bits); otherwise it is presumed to be type bigintif its value fits in type bigint(64 bits); otherwise it is taken to be type numeric. Constants that contain decimal points and/or exponents are always initially presumed to be type numeric.

一个既不包含小数点也不包含指数的数字常量，如果其值符合类型（32 位），则最初假定为类型integer常量integer；否则，bigint如果它的值符合类型bigint（64 位），则假定它是类型 ；否则它被认为是 type numeric。包含小数点和/或指数的常量最初总是被假定为 type numeric。

More explanation and links in this related answer:

此相关答案中的更多解释和链接：

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

• PostgreSQL 错误：函数 to_tsvector（字符变化，未知）不存在

Solution

解决方案

Add explicit casts for the smallintparameters or quote them.

为smallint参数添加显式转换或引用它们。

Demo

演示

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

Incorrect call:

错误调用：

SELECT * FROM f_typetest(1);

Correct calls:

正确调用：

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
Old sqlfiddle.

db<>fiddle here
旧的sqlfiddle。

回答by Clodoaldo Neto

That error means that a function call is only matched by an existing function if all its arguments are of the same type and passed in same order. So if the next f()function

该错误意味着函数调用仅与现有函数匹配，前提是其所有参数均为相同类型并以相同顺序传递。所以如果下一个f()函数

create function f() returns integer as $$ 
    select 1;
$$ language sql;

is called as

被称为

select f(1);

It will error out with

它会出错

ERROR:  function f(integer) does not exist
LINE 1: select f(1);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

because there is no f()function that takes an integer as argument.

因为没有f()将整数作为参数的函数。

So you need to carefully compare what you are passing to the function to what it is expecting. That long list of table columns looks like bad design.

因此，您需要仔细比较传递给函数的内容与期望的内容。一长串表格列看起来很糟糕设计。

回答by n1000

In my particular case the function was actually missing. The error message is the same. I am using the Postgresql plugin PostGISand I had to reinstall that for whatever reason.

在我的特殊情况下，该功能实际上丢失了。错误信息是一样的。我正在使用 Postgresql 插件PostGIS，无论出于何种原因，我都不得不重新安装它。