Error: No function matches the given name and argument types

Question

I try to get this function running. When I use pgadmin and manually call this function with

SELECT calculate_something(7) or SELECT common.calculate_something(7)

ERROR: function calculate_something(integer) doesn't exist hint no function matches the given name and argument types (translated from german)

I already tried to cast the call SELECT calculate_something(cast(7 as bigint));

What is wrong with that function or cast? :/

CREATE OR REPLACE FUNCTION common.calculate_something(id bigint)
  RETURNS real AS
$BODY$
  DECLARE
    some_value_out REAL := 20;
  BEGIN
    -- I already removed that part for bug fixing and return a constant value (20)
    RETURN some_value_out;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Works for me. Check if function is created successfully and check if you are connected to right database. — partlov, Mar 21 '16 at 08:57
And if you are working with autocommit off, make sure you `commit` your `create function` statement — , Mar 21 '16 at 08:58
If you are using an another schema than `public` did you grant all required rights? http://www.postgresql.org/docs/9.4/static/sql-grant.html — devanand, Mar 21 '16 at 09:06
For testing purposes I granted all (`grant all on database postgres to myusername;`). — Kody, Mar 21 '16 at 09:15
Are you perhaps using a program such as navicat and typing this in after selecting a particular schema? A lot of people don't realise that programs like this automatically set the search path, so a non-schema defined function may end up inside something other than intended. Also your select statement does not list the schema in the function call. — Lucas, Mar 21 '16 at 11:02
Never heared of that program :) I'm just using pgadmin and sometimes DataGrip (from jetbrains)... and all I do is executing select statements. not even inserts or deletes. — Kody, Mar 21 '16 at 11:11
I already solved that problem. I moved the function into another schema yesterday and forgot about that. sry guys. — Kody, Mar 21 '16 at 12:38

score 0 · Answer 1 · answered Mar 21 '16 at 11:31

0

Is your function actually called:

stoff_stueckgewicht(id bigint)

Or perhaps:

stoff_stückgewicht(id bigint)

?

The latter is a likely cause of problems if your locale settings are different between debian and PostgreSQL.

Alternatively, go through all the functions in all of the schemas on your database, using pgAdmin III and find the function manually.

answered Mar 21 '16 at 11:31

Patrick

29,357
6
62
90

I renamed the function to avoid to make the explanation for my issue more understandable and forgot that part. sry. – Kody Mar 21 '16 at 12:37

score 0 · Answer 2 · edited May 23 '17 at 10:28

Use this to diagnose your problem:

SELECT n.nspname AS schema, p.proname AS function
     , pg_get_function_identity_arguments(p.oid) As args
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  p.proname LIKE 'calculate%';

How to get function parameter lists (so I can drop a function)

And check your search_path:

How does the search_path influence identifier resolution and the "current schema"

Either way, a bigint function can be called with a numeric literal - defaulting to integer, but there is a registered cast to bigint.

Consider Function Type Resolution:

Error: No function matches the given name and argument types

2 Answers2

Linked