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

User · Answer

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.

User · 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  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    PostgreSQL ERROR  function to tsvector character varying  unknown  does not exist   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 lt  fiddle here Old sqlfiddle

User · Answer

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

[function] No function matches the given name and argument types

The answer is

Examples related to function

Examples related to postgresql

Examples related to types

Examples related to casting

Examples related to plpgsql

Tags