MIME-Version: 1.0
References: <CAEpg1wBxp=25isxj6yt2cf97KtvbwxvJfrJ9qYF8UQxGSBJ93Q@mail.gmail.com>
 <1934172.1753462470@sss.pgh.pa.us>
In-Reply-To: <1934172.1753462470@sss.pgh.pa.us>
From: Rumpi Gravenstein <rgravens@gmail.com>
Date: Fri, 25 Jul 2025 12:59:59 -0400
Message-ID: <CAEpg1wCDL+iYbG7ByjHcgfvJy7m3m-rDp-xKiYovvxeKTbG7iQ@mail.gmail.com>
Subject: Re: PostgreSQL Bug with simple function unexpectedly treating varchar
 parameter as an array
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000fdc0fd063ac3df79"
Archived-At: <https://www.postgresql.org/message-id/CAEpg1wCDL%2BiYbG7ByjHcgfvJy7m3m-rDp-xKiYovvxeKTbG7iQ%40mail.gmail.com>
Precedence: bulk

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

No ... just one version:

xxxx_pub_dev_2_db=3D# SHOW server_version;
 server_version
----------------
 16.9
(1 row)


xxxx_pub_dev_2_db=3D# DROP FUNCTION if exists _sa_setup_role;
DROP FUNCTION
xxxx_pub_dev_2_db=3D# CREATE OR REPLACE FUNCTION _sa_setup_role(
p_role_to_be_granted varchar)
xxxx_pub_dev_2_db-# RETURNS varchar
xxxx_pub_dev_2_db-# LANGUAGE plpgsql
xxxx_pub_dev_2_db-# AS
xxxx_pub_dev_2_db-# $function$
xxxx_pub_dev_2_db$# declare
xxxx_pub_dev_2_db$# begin
xxxx_pub_dev_2_db$# raise info '%',p_role_to_be_granted::varchar;
xxxx_pub_dev_2_db$#   return('Done');
xxxx_pub_dev_2_db$# end;
xxxx_pub_dev_2_db$# $function$;
CREATE FUNCTION
xxxx_pub_dev_2_db=3D# select _sa_setup_role('af_repo_app');
ERROR:  malformed array literal: "af_repo_app"
LINE 1: select _sa_setup_role('af_repo_app');
                              ^
DETAIL:  Array value must start with "{" or dimension information.
xxxx_pub_dev_2_db=3D# select _sa_setup_role('af_repo_app'::varchar);
INFO:  af_repo_app
 _sa_setup_role
----------------
 Done
(1 row)


xxxx_pub_dev_2_db=3D# \df _sa_setup_role
                                      List of functions
 Schema |      Name      | Result data type  |          Argument data types
          | Type
--------+----------------+-------------------+-----------------------------=
-----------+------
 sqlapp | _sa_setup_role | character varying | p_role_to_be_granted
character varying | func
(1 row)

On Fri, Jul 25, 2025 at 12:54=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote=
:

> Rumpi Gravenstein <rgravens@gmail.com> writes:
> > I've been confound by the following behavior that I see in one of our
> > PostgreSQL 16 instances.  In this case I am running this script from
> psql.
>
> I'd bet there is another function named _sa_setup_role() that takes
> some kind of array, and the parser is resolving the ambiguity by
> choosing that one.
>
> "\df _sa_setup_role" would be illuminating.
>
>                         regards, tom lane
>


--=20
Rumpi Gravenstein

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

<div dir=3D"ltr">No ... just one version:<br><br>xxxx_pub_dev_2_db=3D# SHOW=
 server_version;<br>=C2=A0server_version<br>----------------<br>=C2=A016.9<=
br>(1 row)<br><br><br>xxxx_pub_dev_2_db=3D# DROP FUNCTION if exists _sa_set=
up_role;<br>DROP FUNCTION<br>xxxx_pub_dev_2_db=3D# CREATE OR REPLACE FUNCTI=
ON _sa_setup_role( p_role_to_be_granted varchar)<br>xxxx_pub_dev_2_db-# RET=
URNS varchar<br>xxxx_pub_dev_2_db-# LANGUAGE plpgsql<br>xxxx_pub_dev_2_db-#=
 AS<br>xxxx_pub_dev_2_db-# $function$<br>xxxx_pub_dev_2_db$# declare<br>xxx=
x_pub_dev_2_db$# begin<br>xxxx_pub_dev_2_db$# raise info &#39;%&#39;,p_role=
_to_be_granted::varchar;<br>xxxx_pub_dev_2_db$# =C2=A0 return(&#39;Done&#39=
;);<br>xxxx_pub_dev_2_db$# end;<br>xxxx_pub_dev_2_db$# $function$;<br>CREAT=
E FUNCTION<br>xxxx_pub_dev_2_db=3D# select _sa_setup_role(&#39;af_repo_app&=
#39;);<br>ERROR: =C2=A0malformed array literal: &quot;af_repo_app&quot;<br>=
LINE 1: select _sa_setup_role(&#39;af_repo_app&#39;);<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 ^<br>DETAIL: =C2=A0Array value must start with &quot;{&quot; =
or dimension information.<br>xxxx_pub_dev_2_db=3D# select _sa_setup_role(&#=
39;af_repo_app&#39;::varchar);<br>INFO: =C2=A0af_repo_app<br>=C2=A0_sa_setu=
p_role<br>----------------<br>=C2=A0Done<br>(1 row)<br><br><br>xxxx_pub_dev=
_2_db=3D# \df _sa_setup_role<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 List of functions<br>=C2=A0Schema | =C2=A0 =C2=A0 =C2=A0N=
ame =C2=A0 =C2=A0 =C2=A0| Result data type =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0Argument data types =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Type<br>=
--------+----------------+-------------------+-----------------------------=
-----------+------<br>=C2=A0sqlapp | _sa_setup_role | character varying | p=
_role_to_be_granted character varying | func<br>(1 row)</div><br><div class=
=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr=
">On Fri, Jul 25, 2025 at 12:54=E2=80=AFPM Tom Lane &lt;<a href=3D"mailto:t=
gl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex">Rumpi Gravenstein &lt;<a href=3D"mailto:=
rgravens@gmail.com" target=3D"_blank">rgravens@gmail.com</a>&gt; writes:<br=
>
&gt; I&#39;ve been confound by the following behavior that I see in one of =
our<br>
&gt; PostgreSQL 16 instances.=C2=A0 In this case I am running this script f=
rom psql.<br>
<br>
I&#39;d bet there is another function named _sa_setup_role() that takes<br>
some kind of array, and the parser is resolving the ambiguity by<br>
choosing that one.<br>
<br>
&quot;\df _sa_setup_role&quot; would be illuminating.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature">Rumpi Gravenstein<input type=3D"hidden"><input type=3D"hidden"><d=
iv></div></div>

--000000000000fdc0fd063ac3df79--