Can't execute function created in pgAdmin on sql shell: "function ... does not exist"

Question

Below is the error I got:

postgres=# select bcount('Deccan');
ERROR:  function bcount(unknown) does not exist
LINE 1: select bcount('Deccan');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Below is my program in pgAdmin:

create function bcount(brname char(30)) returns int as $$
declare
total_no int;
begin
select count(cno) into total_no from Branch,Ternary where Branch.bid=Ternary.bid and br_name=brname;
return total_no;
end;
$$
language plpgsql

Use `text` instead of `char(30)` as the parameter type. In fat `char` should never be used. — , Sep 12 '21 at 07:06
I have changed the data type to 'Text' but still it's not working. — Suraj Paswan, Sep 12 '21 at 07:14
Are the function definition and select execution done with the same database selected? — brian beuning, Sep 13 '21 at 02:56

score 1 · Answer 1 · answered Sep 13 '21 at 23:33

char(30) is typically not useful as has been commented. See:

Any downsides of using data type "text" for storing strings?

But that's not the problem. A different role or missing privileges could also not be the problem, that would produce a different error message.

Your call:

select bcount('Deccan');

... with an untyped string constant as parameter would be coerced to the type, as long as any function of that name is found in the search_path.

Speaking of which, what was the search_path in your pgAdmin environment? Put differently, what's the schema your function was created in? Check with:

SELECT pronamespace::regnamespace AS schema, proname
     , pg_get_functiondef(oid)
FROM   pg_proc
WHERE  proname = 'bcount';

And what's the search_path in your psql environment? Is the schema of the function contained?

SHOW search_path;

See:

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

It's either that, or you connected to the wrong database. A Postgres database cluster consists of any number of databases. If you don't connect to a particular one, you may end up in the default maintenance database named "postgres". In psql, switch with:

\c my_database

Or you connected to the wrong db cluster altogether.

Can't execute function created in pgAdmin on sql shell: "function ... does not exist"

1 Answers1