public inbox for [email protected]
help / color / mirror / Atom feedRe: CALL and named parameters
10+ messages / 5 participants
[nested] [flat]
* Re: CALL and named parameters
@ 2025-08-06 18:08 Pavel Stehule <[email protected]>
2025-08-07 12:44 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Pavel Stehule @ 2025-08-06 18:08 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: [email protected]
Hi
st 6. 8. 2025 v 19:49 odesílatel Dominique Devienne <[email protected]>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: CALL and named parameters
2025-08-06 18:08 Re: CALL and named parameters Pavel Stehule <[email protected]>
@ 2025-08-07 12:44 ` Dominique Devienne <[email protected]>
2025-08-07 13:30 ` Re: CALL and named parameters David G. Johnston <[email protected]>
2025-08-07 23:14 ` Re: CALL and named parameters Christoph Moench-Tegeder <[email protected]>
0 siblings, 2 replies; 10+ messages in thread
From: Dominique Devienne @ 2025-08-07 12:44 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; +Cc: [email protected]
On Wed, Aug 6, 2025 at 8:09 PM Pavel Stehule <[email protected]> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: CALL and named parameters
2025-08-06 18:08 Re: CALL and named parameters Pavel Stehule <[email protected]>
2025-08-07 12:44 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
@ 2025-08-07 13:30 ` David G. Johnston <[email protected]>
2025-08-07 14:18 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
2025-08-07 14:21 ` Re: CALL and named parameters Tom Lane <[email protected]>
1 sibling, 2 replies; 10+ messages in thread
From: David G. Johnston @ 2025-08-07 13:30 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: Pavel Stehule <[email protected]>; [email protected] <[email protected]>
On Thursday, August 7, 2025, Dominique Devienne <[email protected]> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: CALL and named parameters
2025-08-06 18:08 Re: CALL and named parameters Pavel Stehule <[email protected]>
2025-08-07 12:44 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
2025-08-07 13:30 ` Re: CALL and named parameters David G. Johnston <[email protected]>
@ 2025-08-07 14:18 ` Dominique Devienne <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Dominique Devienne @ 2025-08-07 14:18 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Pavel Stehule <[email protected]>; [email protected] <[email protected]>
On Thu, Aug 7, 2025 at 3:30 PM David G. Johnston
<[email protected]> wrote:
> On Thursday, August 7, 2025, Dominique Devienne <[email protected]> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: CALL and named parameters
2025-08-06 18:08 Re: CALL and named parameters Pavel Stehule <[email protected]>
2025-08-07 12:44 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
2025-08-07 13:30 ` Re: CALL and named parameters David G. Johnston <[email protected]>
@ 2025-08-07 14:21 ` Tom Lane <[email protected]>
2025-08-07 14:26 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2025-08-07 14:21 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Dominique Devienne <[email protected]>; Pavel Stehule <[email protected]>; [email protected] <[email protected]>
"David G. Johnston" <[email protected]> writes:
> On Thursday, August 7, 2025, Dominique Devienne <[email protected]> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: CALL and named parameters
2025-08-06 18:08 Re: CALL and named parameters Pavel Stehule <[email protected]>
2025-08-07 12:44 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
2025-08-07 13:30 ` Re: CALL and named parameters David G. Johnston <[email protected]>
2025-08-07 14:21 ` Re: CALL and named parameters Tom Lane <[email protected]>
@ 2025-08-07 14:26 ` Dominique Devienne <[email protected]>
2025-08-07 14:42 ` Re: CALL and named parameters Tom Lane <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Dominique Devienne @ 2025-08-07 14:26 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pavel Stehule <[email protected]>; [email protected] <[email protected]>
On Thu, Aug 7, 2025 at 4:21 PM Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: CALL and named parameters
2025-08-06 18:08 Re: CALL and named parameters Pavel Stehule <[email protected]>
2025-08-07 12:44 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
2025-08-07 13:30 ` Re: CALL and named parameters David G. Johnston <[email protected]>
2025-08-07 14:21 ` Re: CALL and named parameters Tom Lane <[email protected]>
2025-08-07 14:26 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
@ 2025-08-07 14:42 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2025-08-07 14:42 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pavel Stehule <[email protected]>; [email protected] <[email protected]>
Dominique Devienne <[email protected]> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: CALL and named parameters
2025-08-06 18:08 Re: CALL and named parameters Pavel Stehule <[email protected]>
2025-08-07 12:44 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
@ 2025-08-07 23:14 ` Christoph Moench-Tegeder <[email protected]>
2025-08-07 23:33 ` Re: CALL and named parameters Tom Lane <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Christoph Moench-Tegeder @ 2025-08-07 23:14 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: Pavel Stehule <[email protected]>; [email protected]
## Dominique Devienne ([email protected]):
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: CALL and named parameters
2025-08-06 18:08 Re: CALL and named parameters Pavel Stehule <[email protected]>
2025-08-07 12:44 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
2025-08-07 23:14 ` Re: CALL and named parameters Christoph Moench-Tegeder <[email protected]>
@ 2025-08-07 23:33 ` Tom Lane <[email protected]>
2025-08-08 08:18 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2025-08-07 23:33 UTC (permalink / raw)
To: Christoph Moench-Tegeder <[email protected]>; +Cc: Dominique Devienne <[email protected]>; Pavel Stehule <[email protected]>; [email protected]
Christoph Moench-Tegeder <[email protected]> writes:
> ## Dominique Devienne ([email protected]):
>> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: CALL and named parameters
2025-08-06 18:08 Re: CALL and named parameters Pavel Stehule <[email protected]>
2025-08-07 12:44 ` Re: CALL and named parameters Dominique Devienne <[email protected]>
2025-08-07 23:14 ` Re: CALL and named parameters Christoph Moench-Tegeder <[email protected]>
2025-08-07 23:33 ` Re: CALL and named parameters Tom Lane <[email protected]>
@ 2025-08-08 08:18 ` Dominique Devienne <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Dominique Devienne @ 2025-08-08 08:18 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Christoph Moench-Tegeder <[email protected]>; Pavel Stehule <[email protected]>; [email protected]
On Fri, Aug 8, 2025 at 1:33 AM Tom Lane <[email protected]> wrote:
> Christoph Moench-Tegeder <[email protected]> writes:
> > ## Dominique Devienne ([email protected]):
> >> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2025-08-08 08:18 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-06 18:08 Re: CALL and named parameters Pavel Stehule <[email protected]>
2025-08-07 12:44 ` Dominique Devienne <[email protected]>
2025-08-07 13:30 ` David G. Johnston <[email protected]>
2025-08-07 14:18 ` Dominique Devienne <[email protected]>
2025-08-07 14:21 ` Tom Lane <[email protected]>
2025-08-07 14:26 ` Dominique Devienne <[email protected]>
2025-08-07 14:42 ` Tom Lane <[email protected]>
2025-08-07 23:14 ` Christoph Moench-Tegeder <[email protected]>
2025-08-07 23:33 ` Tom Lane <[email protected]>
2025-08-08 08:18 ` Dominique Devienne <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox