Incidently the fix looks quite simple. See patch attached.
With this patch we have a diffs in create_procedure test like
CALL random(); -- error
! ERROR: random() is not a procedure
LINE 1: CALL random();
^
! HINT: To call a function, use SELECT.
CREATE FUNCTION cp_testfunc1(a int) RETURNS int LANGUAGE SQL AS $$
SELECT a $$;
CREATE TABLE cp_test (a int, b text);
CREATE PROCEDURE ptest1(x text)
--- 4,13 ----
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
CALL random(); -- error
! ERROR: function random() does not exist
LINE 1: CALL random();
^
! HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
CREATE FUNCTION cp_testfunc1(a int) RETURNS int LANGUAGE SQL AS $$
SELECT a $$;
CREATE TABLE cp_test (a int, b text);
CREATE PROCEDURE ptest1(x text)
If we replace "function" with "procedure" the new error messages read
"procedure random() does not exist" "No procedure matches the given
...". Those messages look better than "random() is not a procedure".
But I haven't fixed the error messages in this patch. I need to first
see if the changes are acceptable.
On Fri, Mar 23, 2018 at 3:53 PM, Ashutosh Bapat
<ashutosh.ba...@enterprisedb.com> wrote:
> Hi,
> Consider following scenario
>
> create function foo(a int) returns integer as $$begin return a; end;
> $$ language plpgsql;
> create procedure foo(a float) as $$begin end; $$ language plpgsql;
> call foo(1);
> psql:proc_func_resolution.sql:8: ERROR: foo(integer) is not a procedure
> LINE 1: call foo(1);
> ^
> HINT: To call a function, use SELECT.
>
> to me the error message looks confusing. I am using CALL, which means
> I am trying to invoke a "procedure" not a "function" and there exists
> one which can be invoked. If I drop function foo() and try call again,
> it succeeds.
>
> drop function foo(a int);
> DROP FUNCTION
> call foo(1);
> CALL
>
> Functions and Procedures are two different objects and we enforce
> different methods to invoke those, SELECT and CALL resp. So, we should
> be able to filter out one or the other and try to find best candidate
> of a given kind.
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index 52dd400..2a8b78d 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -901,7 +901,7 @@ TypeIsVisible(Oid typid)
*/
FuncCandidateList
FuncnameGetCandidates(List *names, int nargs, List *argnames,
- bool expand_variadic, bool expand_defaults,
+ bool expand_variadic, bool expand_defaults, char prokind,
bool missing_ok)
{
FuncCandidateList resultList = NULL;
@@ -948,6 +948,10 @@ FuncnameGetCandidates(List *names, int nargs, List *argnames,
int *argnumbers = NULL;
FuncCandidateList newResult;
+ /* Match prokind if specific one is requested. */
+ if (prokind != PROKIND_INVALID && prokind != procform->prokind)
+ continue;
+
if (OidIsValid(namespaceId))
{
/* Consider only procs in specified namespace */
@@ -1409,7 +1413,8 @@ FunctionIsVisible(Oid funcid)
visible = false;
clist = FuncnameGetCandidates(list_make1(makeString(proname)),
- nargs, NIL, false, false, false);
+ nargs, NIL, false, false,
+ PROKIND_INVALID, false);
for (; clist; clist = clist->next)
{
diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c
index 50d8d81..27530b0 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -803,6 +803,7 @@ lookup_agg_function(List *fnName,
*/
fdresult = func_get_detail(fnName, NIL, NIL,
nargs, input_types, false, false,
+ PROKIND_INVALID,
&fnOid, rettype, &retset,
&nvargs, &vatype,
&true_oid_array, NULL);
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index ea5d521..5784691 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -247,6 +247,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
fdresult = func_get_detail(funcname, fargs, argnames, nargs,
actual_arg_types,
!func_variadic, true,
+ proc_call ? PROKIND_PROCEDURE : PROKIND_INVALID,
&funcid, &rettype, &retset,
&nvargs, &vatype,
&declared_arg_types, &argdefaults);
@@ -1316,6 +1317,7 @@ func_get_detail(List *funcname,
Oid *argtypes,
bool expand_variadic,
bool expand_defaults,
+ char prokind,
Oid *funcid, /* return value */
Oid *rettype, /* return value */
bool *retset, /* return value */
@@ -1342,7 +1344,7 @@ func_get_detail(List *funcname,
/* Get list of possible candidates from namespace search */
raw_candidates = FuncnameGetCandidates(funcname, nargs, fargnames,
- expand_variadic, expand_defaults,
+ expand_variadic, expand_defaults, prokind,
false);
/*
@@ -1978,7 +1980,8 @@ LookupFuncName(List *funcname, int nargs, const Oid *argtypes, bool noError)
/* Passing NULL for argtypes is no longer allowed */
Assert(argtypes);
- clist = FuncnameGetCandidates(funcname, nargs, NIL, false, false, noError);
+ clist = FuncnameGetCandidates(funcname, nargs, NIL, false, false,
+ PROKIND_INVALID, noError);
/*
* If no arguments were specified, the name must yield a unique candidate.
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index a007982..6fb93e0 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -94,7 +94,8 @@ regprocin(PG_FUNCTION_ARGS)
* pg_proc entries in the current search path.
*/
names = stringToQualifiedNameList(pro_name_or_oid);
- clist = FuncnameGetCandidates(names, -1, NIL, false, false, false);
+ clist = FuncnameGetCandidates(names, -1, NIL, false, false,
+ PROKIND_INVALID, false);
if (clist == NULL)
ereport(ERROR,
@@ -128,7 +129,8 @@ to_regproc(PG_FUNCTION_ARGS)
* entries in the current search path.
*/
names = stringToQualifiedNameList(pro_name);
- clist = FuncnameGetCandidates(names, -1, NIL, false, false, true);
+ clist = FuncnameGetCandidates(names, -1, NIL, false, false,
+ PROKIND_INVALID, true);
if (clist == NULL || clist->next != NULL)
PG_RETURN_NULL();
@@ -176,7 +178,8 @@ regprocout(PG_FUNCTION_ARGS)
* qualify it.
*/
clist = FuncnameGetCandidates(list_make1(makeString(proname)),
- -1, NIL, false, false, false);
+ -1, NIL, false, false,
+ PROKIND_INVALID, false);
if (clist != NULL && clist->next == NULL &&
clist->oid == proid)
nspname = NULL;
@@ -263,7 +266,8 @@ regprocedurein(PG_FUNCTION_ARGS)
*/
parseNameAndArgTypes(pro_name_or_oid, false, &names, &nargs, argtypes);
- clist = FuncnameGetCandidates(names, nargs, NIL, false, false, false);
+ clist = FuncnameGetCandidates(names, nargs, NIL, false, false,
+ PROKIND_INVALID, false);
for (; clist; clist = clist->next)
{
@@ -302,7 +306,8 @@ to_regprocedure(PG_FUNCTION_ARGS)
*/
parseNameAndArgTypes(pro_name, false, &names, &nargs, argtypes);
- clist = FuncnameGetCandidates(names, nargs, NIL, false, false, true);
+ clist = FuncnameGetCandidates(names, nargs, NIL, false, false,
+ PROKIND_INVALID, true);
for (; clist; clist = clist->next)
{
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index b0559ca..03bf868 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10755,7 +10755,7 @@ generate_function_name(Oid funcid, int nargs, List *argnames, Oid *argtypes,
if (!force_qualify)
p_result = func_get_detail(list_make1(makeString(proname)),
NIL, argnames, nargs, argtypes,
- !use_variadic, true,
+ !use_variadic, true, PROKIND_INVALID,
&p_funcid, &p_rettype,
&p_retset, &p_nvargs, &p_vatype,
&p_true_typeids, NULL);
diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h
index 5f8cf49..f12edc8 100644
--- a/src/include/catalog/namespace.h
+++ b/src/include/catalog/namespace.h
@@ -72,6 +72,7 @@ extern FuncCandidateList FuncnameGetCandidates(List *names,
int nargs, List *argnames,
bool expand_variadic,
bool expand_defaults,
+ char prokind,
bool missing_ok);
extern bool FunctionIsVisible(Oid funcid);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index bfc9009..8821bb7 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5578,6 +5578,7 @@ DESCR("hash partition CHECK constraint");
/*
* Symbolic values for prokind column
*/
+#define PROKIND_INVALID '\0'
#define PROKIND_FUNCTION 'f'
#define PROKIND_AGGREGATE 'a'
#define PROKIND_WINDOW 'w'
diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h
index 2e3810f..737852d 100644
--- a/src/include/parser/parse_func.h
+++ b/src/include/parser/parse_func.h
@@ -39,6 +39,7 @@ extern FuncDetailCode func_get_detail(List *funcname,
List *fargs, List *fargnames,
int nargs, Oid *argtypes,
bool expand_variadic, bool expand_defaults,
+ char prokind,
Oid *funcid, Oid *rettype,
bool *retset, int *nvargs, Oid *vatype,
Oid **true_typeids, List **argdefaults);
