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

Bracknell

This is almost completely unintellibgible, and seems to just be a post of some code. What's the problem you're having? Error messages? Try describing it. (-1 and closevoted "unclear what you're asking" - you've been on SO enough to know better). – Ruck

The question was presented poorly at first, but the OP put in some effort to improve it. And it presents a very interesting problem. So, the initial downvotes are unfounded now I'd say. – Tinderbox

Answer 1

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

Tinderbox

why SELECT * FROM f_typetest('1'); will work? '1' is a string. Can you please further elaborate it? – Stevenstevena

@Jian: Please read again, focussing on "string constant" vs. "numeric constant" – Tinderbox

Answer 2

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.

Coinstantaneous

Answer 3

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

Buzzard

Your answer might be irrelevant. Obviously his/her function was clearly defined. – Cardinale

Answer 4

I reached this question through Google Search - I encountered the same error

No function matches the given name and argument types

But different to the OP, in my case it is because the function exists, but it is in a different schema.

To verify this case, one need to run the query:

SELECT
n.nspname AS function_schema,
p.proname AS function_name
FROM
pg_proc p
LEFT JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE
n.nspname NOT IN ('pg_catalog', 'information_schema')
AND p.proname ILIKE '%your_function_name%'
ORDER BY
function_schema,
function_name;

Just in case someone has the same issue.

Jannelle

Answer 5

I had the same problem, where the function's parameters matched exactly and it kept getting the error.

After a while, I figured out that the problem was in schema, I had not set the schema in the function and for that, it threw an error.

So it when from:

SELECT my_function(par1, par2, part3)
FROM my_scheme.my_table

To:

SELECT my_schema.my_function(par1, par2, part3)
FROM my_schema.my_table

Frippery

Answer 6

You can also use the type cast operator ::smallint where the assumed integer type is incorrect.

create or replace procedure FnUpdateSalegtab09(
    iDisType smallint
)

call FnUpdateSalegtab09(1::smallint);

Whomp

Answer 7

I created a PL/pgSQL function with OUT parameter as shown below::

CREATE FUNCTION my_func(OUT value INTEGER) AS $$
BEGIN                -- ↑ ↑ ↑ Here ↑ ↑ ↑
END;
$$ LANGUAGE plpgsql;

Then, calling my_func(3) got the same error as shown below because OUT parameter cannot get a value from the caller while it can return a value to the caller:

postgres=# SELECT my_func(3);
ERROR:  function my_func(integer) does not exist
LINE 1: SELECT my_func(3);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

So, I set the INOUT parameter which can get a value from the caller and return a value to the caller as shown below, then the error was solved:

CREATE FUNCTION my_func(INOUT value INTEGER) AS $$
BEGIN                -- ↑ ↑ ↑ Here ↑ ↑ ↑
END;
$$ LANGUAGE plpgsql;

Or, I set the IN parameter which can get a value from the caller but cannot return a value to the caller and RETURNS VOID as shown below, then the error was solved:

CREATE FUNCTION my_func(IN value INTEGER) RETURNS VOID AS $$
BEGIN                -- ↑ ↑ ↑ Here ↑ ↑ ↑
END;
$$ LANGUAGE plpgsql;

Or, I set the parameter without IN keyword which is also recognized as an IN parameter and RETURNS VOID as shown below, then the error was solved:

CREATE FUNCTION my_func(value INTEGER) RETURNS VOID AS $$
BEGIN                -- ↑ ↑ Here ↑ ↑   ↑ ↑ Here ↑ ↑
END;
$$ LANGUAGE plpgsql;

Tristich