"function does not exist," but I really think it does

Question

Am I crazy or just plain dumb?

dev=# \df abuse_resolve 
List of functions
-[ RECORD 1 ]-------+------------------------------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | abuse_resolve
Result data type    | record
Argument data types | INOUT __abuse_id bigint, OUT __msg character varying
Type                | normal

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

Here's the CREATE FUNCTION, I've omitted the meat of the code, but that should be irrelevant:

CREATE OR REPLACE FUNCTION abuse_resolve(INOUT __abuse_id bigint, OUT __msg character varying) RETURNS record AS $_$
DECLARE
    __abuse_status    VARCHAR;
BEGIN
 ...snip...
    UPDATE abuse SET abuse_status    = __abuse_status,
                       edate    = now(),
                       closed_on = now()
                 WHERE abuse_id        = __abuse_id;
    __msg = 'SUCCESS';
END;
$_$ LANGUAGE plpgsql SECURITY DEFINER;

And just for giggles:

GRANT ALL ON FUNCTION abuse_resolve(INOUT __abuse_id, OUT __msg character varying) TO PUBLIC;
GRANT ALL ON FUNCTION abuse_resolve(INOUT __abuse_id, OUT __msg character varying) TO myuser;

That function seems like it exists. What could I be missing?

This is resolved, the answer is: I'm dumb. I had improperly defined the arguments originally, but my code was using the correct ones. There was an extra bigint that had no business being there.

Did you execute both in the same session? Same user? Same search_path? — Erwin Brandstetter, Mar 13 '12 at 15:32
Yes, there's only one user. search_path is set to public, SHOW confirms this. I'll edit with CREATE FUNCTION — echtish, Mar 14 '12 at 04:06

araqnid · Accepted Answer · 2012-03-13T13:41:41.163

4

Well, something is odd. I did:

steve@steve@[local] =# create function abuse_resolve(inout __abuse_id bigint,
                               out __msg text) returns record language plpgsql as
                               $$ begin __msg = 'ok'; end; $$;
CREATE FUNCTION
steve@steve@[local] =# \df abuse_resolve
List of functions
-[ RECORD 1 ]-------+----------------------------------------
Schema              | so9679418
Name                | abuse_resolve
Result data type    | record
Argument data types | INOUT __abuse_id bigint, OUT __msg text
Type                | normal

steve@steve@[local] =# select abuse_resolve('30'::bigint);
-[ RECORD 1 ]-+--------
abuse_resolve | (30,ok)

Have you had any other issues with this database? Can you copy it with dump/restore and try this on the new copy? Does explicitly qualifying the function name with the "public" schema help? Which version of PostgreSQL are you using?

update: sql function It also worked fine for me using:

create function abuse_resolve(inout __abuse_id bigint, out __msg text)
  language sql as $$ select $1, 'ok'::text $$;

edited Mar 13 '12 at 13:41

answered Mar 13 '12 at 11:48

araqnid

127,052
24
157
134

I was able to reproduce @echtish creating the function as sql without `returns record`. Then I saw your sample as plpgsql and dropped my own and created yours and it worked. Then dropped yours and I now can't reproduce the problem anymore with the same function with which I was reproducing the problem at first. I'm using 9.1 on Fedora 16 – Clodoaldo Neto Mar 13 '12 at 12:31
The only sensible explanation is that the `search_path` does not include the `public` schema. I bet that `select public.abuse_resolve('30'::bigint);` works. – Erwin Brandstetter Mar 13 '12 at 12:44
@Clodoaldo: I can't reproduce it using a SQL function either (9.1, Debian) – araqnid Mar 13 '12 at 13:42
@ErwinBrandstetter: but in that case, I would not expect the function to be findable with `\df`, which applies `pg_function_is_visible`. – araqnid Mar 13 '12 at 13:43
You are right, it makes no sense. `\df` would not show the function then. – Erwin Brandstetter Mar 13 '12 at 14:00
Version is 9.1.2. I've tried dumping with -c and re-importing with the same result. I have similar functions defined that perform similar tasks (abuse_deny) that work without problem. They use the same parameters, were defined the same way. I think there's something silly I'm overlooking. – echtish Mar 14 '12 at 04:24

score 4 · Answer 2 · answered Apr 02 '12 at 07:59

4

If you can and if is that problem. I recommend to use

"set search_path = mainSchemaName, secondOnes"

to set correct schema where function is created or in a place where you call it directly specify the schema name

select schemaName.abuse_resolve('30'::bigint);

answered Apr 02 '12 at 07:59

Perlos

2,028
6
27
37

This is it. right here. I forgot to define my schema name before my function and that was causing it to not work. Thanks! – Daniel L. VanDenBosch Dec 04 '17 at 13:40

score 1 · Answer 3 · answered Mar 13 '12 at 06:47

1

Try this syntax:

SELECT * FROM abuse_resolve('30'::bigint);

answered Mar 13 '12 at 06:47

John P

15,035
4
48
56

Thanks, but no joy. Still, `ERROR: function abuse_resolve(bigint) does not exist` – echtish Mar 13 '12 at 07:11

score 0 · Answer 4 · answered Mar 03 '21 at 08:09

0

I had everything but no usage on the schema. Granting usage on schema fixed it.

answered Mar 03 '21 at 08:09

viggy28

760
1
10
21

"function does not exist," but I really think it does

4 Answers4

Linked