Subject: Re: CALL and named parameters

From: Pavel Stehule <[email-masked]>
Date: Wed, 6 Aug 2025 20:08:36 +0200

Hi

st 6. 8. 2025 v 19:49 odesílatel Dominique Devienne <[email-masked]>
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

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 7 Aug 2025 14:44:51 +0200

> 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

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 7 Aug 2025 06:30:30 -0700

> 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.

> 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.

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 7 Aug 2025 16:18:43 +0200

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

Thanks for confirming.

>> So the function did exist, with the correct "signature".

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.

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

---

From: Tom Lane <[email-masked]>
Date: Thu, 07 Aug 2025 10:21:31 -0400

> 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

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 7 Aug 2025 16:26:34 +0200

> 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.

> 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

---

From: Tom Lane <[email-masked]>
Date: Thu, 07 Aug 2025 10:42:15 -0400

> 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

---

From: Christoph Moench-Tegeder <[email-masked]>
Date: Fri, 8 Aug 2025 01:14:07 +0200

> 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

---

From: Tom Lane <[email-masked]>
Date: Thu, 07 Aug 2025 19:33:25 -0400

> "too flexible", it can be coerced into too many types, so the error

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

---

From: Dominique Devienne <[email-masked]>
Date: Fri, 8 Aug 2025 10:18:28 +0200

> parameter just fine, except that we never considered this procedure

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