No function matches the given name and argument types

21

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

This question is tagged with function postgresql types casting plpgsql

~ Asked on 2014-07-15 06:33:44

The Best Answer is


34

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

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

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

A numeric constant that contains neither a decimal point nor an exponent is initially presumed to be type integer if its value fits in type integer (32 bits); otherwise it is presumed to be type bigint if 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.

More explanation and links in this related answer:

Solution

Add explicit casts for the smallint parameters or quote them.

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.

~ Answered on 2014-07-16 02:09:34


1

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

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.

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.

~ Answered on 2014-07-15 08:49:17


Most Viewed Questions: