ERROR: function ... does not exist and HINT: No function matches the given name and argument types

Question

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, c…

Accepted Answer

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. The manual:

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`.

Also see:

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

Solution

Add explicit casts for the `smallint` parameters or pass quoted (untyped) literals.

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.

ERROR: function ... does not exist and HINT: No function matches the given name and argument types

Solution

Problem

Related problems