MIME-Version: 1.0
References: <CAFCRh-_iLoUtMAtyunw_-O6sgpWo04sOmB38MUVNpuQVSkL_0Q@mail.gmail.com>
In-Reply-To: <CAFCRh-_iLoUtMAtyunw_-O6sgpWo04sOmB38MUVNpuQVSkL_0Q@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Wed, 6 Aug 2025 20:08:36 +0200
Message-ID: <CAFj8pRCyW4E5OnZDGvk_YnPqaS_PfK-VKJOKGndC-gtAtDOtBw@mail.gmail.com>
Subject: Re: CALL and named parameters
To: Dominique Devienne <ddevienne@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000fe6317063bb63c44"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRCyW4E5OnZDGvk_YnPqaS_PfK-VKJOKGndC-gtAtDOtBw%40mail.gmail.com>
Precedence: bulk

--000000000000fe6317063bb63c44
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

st 6. 8. 2025 v 19:49 odes=C3=ADlatel Dominique Devienne <ddevienne@gmail.c=
om>
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=3D# 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=3D# call foo(10,20);
NOTICE:  a: 10, b: 20
CALL
(2025-08-06 20:05:20) postgres=3D# call foo(10,b=3D>20);
NOTICE:  a: 10, b: 20
CALL
(2025-08-06 20:05:26) postgres=3D# call foo(a=3D>10,b=3D>20);
NOTICE:  a: 10, b: 20
CALL
(2025-08-06 20:05:33) postgres=3D# 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=3D# call foo1(a=3D>10);
NOTICE:  a: 10, b: 0.0
CALL
(2025-08-06 20:05:57) postgres=3D# call foo(b=3D>20, a=3D>10);
NOTICE:  a: 10, b: 20
CALL
(2025-08-06 20:06:13) postgres=3D#

Maybe there is another issue?

Can you send an example?

Regards

Pavel

--000000000000fe6317063bb63c44
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi</div><br><div class=3D"gmail_quote gmail_quote_con=
tainer"><div dir=3D"ltr" class=3D"gmail_attr">st 6. 8. 2025 v=C2=A019:49 od=
es=C3=ADlatel Dominique Devienne &lt;<a href=3D"mailto:ddevienne@gmail.com"=
>ddevienne@gmail.com</a>&gt; napsal:<br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex">(sorry, this is a rant...).<br>
<br>
Was getting an error calling a procedure<br>
<br>
ERROR:=C2=A0 procedure ... does not exist<br>
HINT:=C2=A0 No procedure matches the given name and argument types. You<br>
might need to add explicit type casts.<br>
<br>
I verify USAGE on the SCHEMA of the proc. OK.<br>
I verify EXECUTE on the FUNCTION. OK.<br>
I verify the names of the parameters, in my CALL with named arguments. OK.<=
br>
<br>
Turns out, thanks to ChatGPT for clueing me in, CALL does NOT support<br>
named parameters. And it&#39;s about the least helpful error message<br>
PostgreSQL could have provided IMO. I&#39;d expect something much better<br=
>
in this specific case, FWIW.<br>
<br>
That&#39;s two unhelpful error messages in a short time :).<br>
<br>
Thanks, and again sorry for the rant. Wasted time on this. --DD<br></blockq=
uote><div><br></div><div>I think so ChatGPT is wrong</div><div><br></div><d=
iv>(2025-08-06 20:04:34) postgres=3D# create or replace procedure foo(a int=
, b numeric)<br>postgres-# as $$ begin<br>postgres$# =C2=A0 raise notice &#=
39;a: %, b: %&#39;, a, b;<br>postgres$# end;<br>postgres$# $$ language plpg=
sql;<br>CREATE PROCEDURE<br>(2025-08-06 20:05:15) postgres=3D# call foo(10,=
20);<br>NOTICE: =C2=A0a: 10, b: 20<br>CALL<br>(2025-08-06 20:05:20) postgre=
s=3D# call foo(10,b=3D&gt;20);<br>NOTICE: =C2=A0a: 10, b: 20<br>CALL<br>(20=
25-08-06 20:05:26) postgres=3D# call foo(a=3D&gt;10,b=3D&gt;20);<br>NOTICE:=
 =C2=A0a: 10, b: 20<br>CALL<br>(2025-08-06 20:05:33) postgres=3D# create or=
 replace procedure foo1(a int, b numeric default 0.0)<br>as $$ begin<br>=C2=
=A0 raise notice &#39;a: %, b: %&#39;, a, b;<br>end;<br>$$ language plpgsql=
;<br>CREATE PROCEDURE<br>(2025-08-06 20:05:49) postgres=3D# call foo1(a=3D&=
gt;10);<br>NOTICE: =C2=A0a: 10, b: 0.0<br>CALL<br>(2025-08-06 20:05:57) pos=
tgres=3D# call foo(b=3D&gt;20, a=3D&gt;10);<br>NOTICE: =C2=A0a: 10, b: 20<b=
r>CALL<br>(2025-08-06 20:06:13) postgres=3D#=C2=A0<br></div><div><br></div>=
<div>Maybe there is another issue?</div><div><br></div><div>Can you send an=
 example?</div><div><br></div><div>Regards</div><div><br></div><div>Pavel</=
div><div><br></div><div>=C2=A0</div></div></div>

--000000000000fe6317063bb63c44--