No function matches the given name and argument types
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.
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 typeinteger
(32 bits); otherwise it is presumed to be typebigint
if its value fits in typebigint
(64 bits); otherwise it is taken to be typenumeric
. Constants that contain decimal points and/or exponents are always initially presumed to be typenumeric
.
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.