CALL and named parameters

Hi

st 6. 8. 2025 v 19:49 odesílatel Dominique Devienne <ddevienne@gmail.com>
napsal:

(sorry, this is a rant...).

Was getting an error calling a procedure

ERROR: procedure ... does not exist
HINT: No procedure matches the given name and argument types. You
might need to add explicit type casts.

I verify USAGE on the SCHEMA of the proc. OK.
I verify EXECUTE on the FUNCTION. OK.
I verify the names of the parameters, in my CALL with named arguments. OK.

Turns out, thanks to ChatGPT for clueing me in, CALL does NOT support
named parameters. And it's about the least helpful error message
PostgreSQL could have provided IMO. I'd expect something much better
in this specific case, FWIW.

That's two unhelpful error messages in a short time :).

Thanks, and again sorry for the rant. Wasted time on this. --DD

I think so ChatGPT is wrong

(2025-08-06 20:04:34) postgres=# create or replace procedure foo(a int, b
numeric)
postgres-# as $$ begin
postgres$# raise notice 'a: %, b: %', a, b;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE PROCEDURE
(2025-08-06 20:05:15) postgres=# call foo(10,20);
NOTICE: a: 10, b: 20
CALL
(2025-08-06 20:05:20) postgres=# call foo(10,b=>20);
NOTICE: a: 10, b: 20
CALL
(2025-08-06 20:05:26) postgres=# call foo(a=>10,b=>20);
NOTICE: a: 10, b: 20
CALL
(2025-08-06 20:05:33) postgres=# create or replace procedure foo1(a int, b
numeric default 0.0)
as $$ begin
raise notice 'a: %, b: %', a, b;
end;
$$ language plpgsql;
CREATE PROCEDURE
(2025-08-06 20:05:49) postgres=# call foo1(a=>10);
NOTICE: a: 10, b: 0.0
CALL
(2025-08-06 20:05:57) postgres=# call foo(b=>20, a=>10);
NOTICE: a: 10, b: 20
CALL
(2025-08-06 20:06:13) postgres=#

Maybe there is another issue?

Can you send an example?

Regards

Pavel

On 8/6/25 10:48, Dominique Devienne wrote:

(sorry, this is a rant...).

Was getting an error calling a procedure

ERROR: procedure ... does not exist
HINT: No procedure matches the given name and argument types. You
might need to add explicit type casts.

I verify USAGE on the SCHEMA of the proc. OK.
I verify EXECUTE on the FUNCTION. OK.
I verify the names of the parameters, in my CALL with named arguments. OK.

Turns out, thanks to ChatGPT for clueing me in, CALL does NOT support
named parameters. And it's about the least helpful error message
PostgreSQL could have provided IMO. I'd expect something much better
in this specific case, FWIW.

I am not following as:

CREATE OR REPLACE PROCEDURE public.call_test(IN a integer, IN b integer)
LANGUAGE plpgsql
AS $procedure$
BEGIN
RAISE NOTICE 'c = %', a + b;
END;
$procedure$

call call_test (a=>1, b=>2);
NOTICE: c = 3
CALL

You will need to provide more information on your specific case.

That's two unhelpful error messages in a short time :).

Thanks, and again sorry for the rant. Wasted time on this. --DD

--
Adrian Klaver
adrian.klaver@aklaver.com

## Dominique Devienne (ddevienne@gmail.com):

Turns out, thanks to ChatGPT for clueing me in, CALL does NOT support
named parameters.

Turns out, ChatGPT is once again very wrong.
As per https://www.postgresql.org/docs/14/sql-call.html ,
"Arguments can include parameter names, using the syntax name => value."
So, if you'd include what you actually did to get that error message...
And maybe try the manual, it's really good (we think).

Regards,
Christoph

--
Spare Space

On Wed, Aug 6, 2025 at 8:09 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:

Maybe there is another issue?

Indeed. Thanks Pavel, Andrian, Christoph, for demonstrating I was wrong.
I misinterpreted the signals I got, and accepted the AI's interpretation on
success (after a long day) when the inderlying isse was elsewhere.

My mistake was in

I verify the names of the parameters, in my CALL with named arguments. OK.

I verified against the embedded PL/pgSQL in my code, not what was in the DB.
I had renamed an argument in the code, but the schema was instantiated
using the earlier code.
And because I was using the named-argument syntax, but a wrong
arg-name, it fails.

What's not nice is in the way it failed IMHO. I guess I persist it's
not a user friendly message :)

Can you overload a function solely by changing an argument name?
If not, as I suspect, then function lookup doesn't strictly depend on
argument names (like in C++).
So the function did exist, with the correct "signature" (ignoring
argument names).
And I was "just" using the wrong arg-name. That tripped me up.

The AI's suggestion, to go positional, while based on crap reasoning,
did help me, in a way :).

So mea culpa. Apologies for the misguided rant (and smaller re-rant above :)).

PS: below's my psql session that led me to the wrong conclusion.

dd_v185=> call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe');
ERROR: procedure Epos-DBA.db_grant_connect_to(grantee_role =>
unknown) does not exist
LINE 1: call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe'...
^
HINT: No procedure matches the given name and argument types. You
might need to add explicit type casts.
dd_v185=> call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe'::name);
ERROR: procedure Epos-DBA.db_grant_connect_to(grantee_role => name)
does not exist
LINE 1: call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe'...
^
HINT: No procedure matches the given name and argument types. You
might need to add explicit type casts.
dd_v185=> \df "Epos-DBA".db_grant_connect_to
List of functions
Schema | Name | Result data type | Argument data types | Type
----------+---------------------+------------------+---------------------+------
Epos-DBA | db_grant_connect_to | | IN login_role name | proc
(1 row)

dd_v185=> select has_schema_privilege('Epos-DBA', 'usage');
has_schema_privilege
----------------------
t
(1 row)

dd_v185=> select
has_function_privilege('"Epos-DBA".db_grant_connect_to(name)',
'execute');
has_function_privilege
------------------------
t
(1 row)

dd_v185=> call "Epos-DBA".db_grant_connect_to('dd_joe');
CALL

On Thursday, August 7, 2025, Dominique Devienne <ddevienne@gmail.com> wrote:

What's not nice is in the way it failed IMHO. I guess I persist it's
not a user friendly message :)

Then write the error message you would have liked to see.

Can you overload a function solely by changing an argument name?

No, the signature is only the name and input argument types.

If not, as I suspect, then function lookup doesn't strictly depend on
argument names (like in C++).
So the function did exist, with the correct "signature" (ignoring
argument names).
And I was "just" using the wrong arg-name. That tripped me up.

How is it “just” an argument name when you are using named argument syntax?

David J.

Hi

čt 7. 8. 2025 v 15:30 odesílatel David G. Johnston <
david.g.johnston@gmail.com> napsal:

On Thursday, August 7, 2025, Dominique Devienne <ddevienne@gmail.com>
wrote:

What's not nice is in the way it failed IMHO. I guess I persist it's
not a user friendly message :)

Then write the error message you would have liked to see.

Can you overload a function solely by changing an argument name?

No, the signature is only the name and input argument types.

If not, as I suspect, then function lookup doesn't strictly depend on
argument names (like in C++).
So the function did exist, with the correct "signature" (ignoring
argument names).
And I was "just" using the wrong arg-name. That tripped me up.

How is it “just” an argument name when you are using named argument syntax?

David J.

(2025-08-07 15:58:24) postgres=# select fx(b=>10);
ERROR: function fx(b => integer) does not exist
LINE 1: select fx(b=>10);
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.

The error message and hint is simillary unfriendly like for a case with
unnamed parameters. I am afraid that implementing a more friendly error
message can slow down the query execution :-/. Now we raise errors when we
know, so we didn't find a good signature, but we don't know what is wrong,
so it is difficult to raise errors in the sense that the name of the
argument is wrong.

Regards

Pavel

On Thu, Aug 7, 2025 at 3:30 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Thursday, August 7, 2025, Dominique Devienne <ddevienne@gmail.com> wrote:

Can you overload a function solely by changing an argument name?

No, the signature is only the name and input argument types.

Thanks for confirming.

So the function did exist, with the correct "signature".
And I was "just" using the wrong arg-name. That tripped me up.

How is it “just” an argument name when you are using named argument syntax?

I was expecting an error telling me the procedure exists, but the
argument name used in the call didn't. Then it's obvious to me what
mistake I made. If argument names don't participate in the function's
signature, why should they participate in the lookup? Do the lookup
based on name and arg types (and count), that gives you a possible
overload set, which in my second attempt (with a ::name cast) WAS AN
EXACT MATCH for the signature, then give me an error about the
argument name, that does NOT tell me the function doesn't exist.
That's what I would expect.

Now, as a dev, I understand that my own experience is a tiny subcase
of a larger problem. I'm sure it can be super complex in the general
case.

HINT: No procedure matches the given name and argument types. You
might need to add explicit type casts.

in "given name and argument types", `name` applies to the procedure
name? Was how I read it.
Or it ALSO applies to types too (wasn't my interpretation).
The hint mentions casts, which I tried, to no avail.
The hint does NOT mention check the arg-names, especially since it
knows I'm using named-arguments at the call side.

I'm saying that's a poor user experience. Yes it's my error. I should
have known better, yadi yadi yada.

When you try to DROP an object with privileges on some objects, it
lists you those objects.

Here, it doesn't even lists you the candidates from the overload set.
With param names, if using named argument.

We can agree to disagree. PostgreSQL is OSS and all. I'm just telling
you, and the community at large, that this error is misleading IMHO,
and that it tripped me up, and I'm making noise in the hope it gets
improved, so the next user (probably me!) that runs into it next, has
a better chance of not wasting a few hours on that one.

Thanks, --DD

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thursday, August 7, 2025, Dominique Devienne <ddevienne@gmail.com> wrote:

What's not nice is in the way it failed IMHO. I guess I persist it's
not a user friendly message :)

Then write the error message you would have liked to see.

The message presumably was like

ERROR: procedure ... does not exist
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.

That HINT hasn't been updated since we added named arguments, but
"argument names don't match" is now also a possible failure reason.
The simplest possible change would be, say,

HINT: No procedure matches the given name and argument names/types. You might need to add explicit type casts.

Not sure if that's good enough, but the matching rules are
complex enough that it'd be hard to be definitive about
the argument name being the problem.

regards, tom lane

On Thu, Aug 7, 2025 at 4:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

The message presumably was like

ERROR: procedure ... does not exist
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.

Hi Tom.

Indeed it was. I shared my psql session showing that.

That HINT hasn't been updated since we added named arguments, but
"argument names don't match" is now also a possible failure reason.
The simplest possible change would be, say,

HINT: No procedure matches the given name and argument names/types. You might need to add explicit type casts.

Not sure if that's good enough, but the matching rules are
complex enough that it'd be hard to be definitive about
the argument name being the problem.

That's better than nothing. And I get it's likely complex.

But still, arg names are not part of the signature.
So they should be checked after the fact.

In my case, the SCHEMA eas explicit, so no search_path.
With the ::name cast, the signature was an exact match.
And there are no overloads at all.

So it's possibly the "worse case" for saying the proc does NOT exist...
It's like the code should do a LEFT JOIN instead of a JOIN on arg names :).

--DD

Dominique Devienne <ddevienne@gmail.com> writes:

I was expecting an error telling me the procedure exists, but the
argument name used in the call didn't. Then it's obvious to me what
mistake I made. If argument names don't participate in the function's
signature, why should they participate in the lookup? Do the lookup
based on name and arg types (and count), that gives you a possible
overload set, which in my second attempt (with a ::name cast) WAS AN
EXACT MATCH for the signature, then give me an error about the
argument name, that does NOT tell me the function doesn't exist.
That's what I would expect.

Criticism in the form of a patch is welcome ;-). The problem here
is that the matching rules are far more complicated than you seem
to think. It's not clear to me that we can definitively say that
"we would have had a match except the argument name was wrong".
If it is possible to know that, it's quite a few subroutines below
where the error is actually issued (see ParseFuncOrColumn ->
func_get_detail -> FuncnameGetCandidates -> MatchNamedCall).
Maybe there's some fairly painless change that could be made in
that rat's nest, but I lack enough caffeine to see it.

regards, tom lane

On Thu, Aug 7, 2025 at 4:36 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dominique Devienne <ddevienne@gmail.com> writes:

I was expecting an error telling me the procedure exists, but the
argument name used in the call didn't. Then it's obvious to me what
mistake I made. If argument names don't participate in the function's
signature, why should they participate in the lookup? Do the lookup
based on name and arg types (and count), that gives you a possible
overload set, which in my second attempt (with a ::name cast) WAS AN
EXACT MATCH for the signature, then give me an error about the
argument name, that does NOT tell me the function doesn't exist.
That's what I would expect.

Criticism in the form of a patch is welcome ;-). The problem here
is that the matching rules are far more complicated than you seem
to think. It's not clear to me that we can definitively say that
"we would have had a match except the argument name was wrong".
If it is possible to know that, it's quite a few subroutines below
where the error is actually issued (see ParseFuncOrColumn ->
func_get_detail -> FuncnameGetCandidates -> MatchNamedCall).
Maybe there's some fairly painless change that could be made in
that rat's nest, but I lack enough caffeine to see it.

How about listing those "candidates" considered? In the detailed message?
With arg names. Then it would have been obvious(er), I'm arguing. --DD

Dominique Devienne <ddevienne@gmail.com> writes:

But still, arg names are not part of the signature.
So they should be checked after the fact.

No, that's not how it works. David's comment about signature
reflects the fact that the primary key of pg_proc is name +
schema + input argument types. Arg names are independent of
that and actually have to be checked before we consider argument
type matching, because they help determine which input argument
is which.

(There was considerable debate when we added output arguments
and argument names about whether pg_proc's primary key should
be extended. We ended up not, but it was a judgment call.)

regards, tom lane

## Dominique Devienne (ddevienne@gmail.com):

dd_v185=> call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe');
ERROR: procedure Epos-DBA.db_grant_connect_to(grantee_role =>
unknown) does not exist
LINE 1: call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe'...

There's the problem: "unknown" type - consider that string there as
"too flexible", it can be coerced into too many types, so the error
says "unknown".
Consider this demonstration:

db=# call proc1(val => 1);
CALL
db=# call proc1(val => '2');
CALL
db=# call proc1(value => 3);
ERROR: procedure proc1(value => integer) does not exist
LINE 1: call proc1(value => 3);
^
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.
db=# call proc1(value => '4');
ERROR: procedure proc1(value => unknown) does not exist
LINE 1: call proc1(value => '4');
^
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.
db=# call proc1(value => text '5');
ERROR: procedure proc1(value => text) does not exist
LINE 1: call proc1(value => text '5');
^
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.

Regards,
Christoph

--
Spare Space

Christoph Moench-Tegeder <cmt@burggraben.net> writes:

## Dominique Devienne (ddevienne@gmail.com):

dd_v185=> call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe');
ERROR: procedure Epos-DBA.db_grant_connect_to(grantee_role =>
unknown) does not exist
LINE 1: call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe'...

There's the problem: "unknown" type - consider that string there as
"too flexible", it can be coerced into too many types, so the error
says "unknown".

No, the issue is that the procedure's named parameter is not named
"grantee_role" but something else. We'd have coerced the unknown
parameter just fine, except that we never considered this procedure
as a valid match at all.

regards, tom lane

On Fri, Aug 8, 2025 at 1:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Christoph Moench-Tegeder <cmt@burggraben.net> writes:

## Dominique Devienne (ddevienne@gmail.com):

dd_v185=> call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe');
ERROR: procedure Epos-DBA.db_grant_connect_to(grantee_role =>
unknown) does not exist
LINE 1: call "Epos-DBA".db_grant_connect_to(grantee_role => 'dd_joe'...

There's the problem: "unknown" type - consider that string there as
"too flexible", it can be coerced into too many types, so the error
says "unknown".

No, the issue is that the procedure's named parameter is not named
"grantee_role" but something else. We'd have coerced the unknown
parameter just fine, except that we never considered this procedure
as a valid match at all.

That. And it failed just the same with a ::name cast, so no "unknown"
type there.
And in all of that, there's a single "Epos-DBA".db_grant_connect_to(...).
So it's not like the "candidates" are a large set. --DD