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.
4 Answers 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);
1 Comment
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 $$;
6 Comments
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 16search_path does not include the public schema. I bet that select public.abuse_resolve('30'::bigint); works.\df, which applies pg_function_is_visible.\df would not show the function then.Try this syntax:
SELECT * FROM abuse_resolve('30'::bigint);
1 Comment
ERROR: function abuse_resolve(bigint) does not existI had everything but no usage on the schema. Granting usage on schema fixed it.
create functionstatement?