Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ufLtU-0065OL-TS for pgsql-general@arkaria.postgresql.org; Fri, 25 Jul 2025 17:07:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ufLtS-0034nu-VZ for pgsql-general@arkaria.postgresql.org; Fri, 25 Jul 2025 17:07:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ufLtS-0034nj-Je for pgsql-general@lists.postgresql.org; Fri, 25 Jul 2025 17:07:38 +0000 Received: from mail-lf1-x12f.google.com ([2a00:1450:4864:20::12f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ufLtP-000oGC-23 for pgsql-general@lists.postgresql.org; Fri, 25 Jul 2025 17:07:38 +0000 Received: by mail-lf1-x12f.google.com with SMTP id 2adb3069b0e04-55a4e55d3a9so2591962e87.1 for ; Fri, 25 Jul 2025 10:07:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1753463254; x=1754068054; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=qY+yKCy7gwRxFs7D0EH0Gl2hYSqifY1y7Q94Sc3V6I0=; b=ZAmiBXpyzUvzdr4qQPwK60JaHfGmoiQVmg4txUshiMniIF0FL6DSu+UDCv1XjGCmk9 waeIxMBULyfOwBRqv27+W0vme/cxjjVs5tpSD2Bt1bquiNLNqPXMetaatEpfCIWCAuBJ DxNTym9B46gOk7LTFlpzugXatS/WLwNSuPo9zS4ietVWKGsxubb2Iz0MJ0RWrun/6ADJ HOQrXlohsLzQlV0Aw0c1/gP1fM6g56EoaERdyDPyIzNRo3l33mGshQJ6ZaCd3tJE/xPv lPARrxu9C6x4SMl8fg69R5TKYelHsA9JA2iuSAIgSZFH7nUmtXpV66DDsXMbf5Z9j6LI yFzg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1753463254; x=1754068054; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=qY+yKCy7gwRxFs7D0EH0Gl2hYSqifY1y7Q94Sc3V6I0=; b=Oa1fpZIDbra8XvruME9cad/FKJvtyfQRHoT3PYeQbrdB/t0p3Em+X/8B+erz+aep8X wBTirWKLiGXCLn+GP57HdS3bGxF0B1Z9rGaaJ70VjokCZ0zyQAYwM88nr0G03hxUjoNK fQXWtGB6MZFIbT5/UnuVTJo2bDQxc6q2g14icdBamZhLcCPXJH3csQzuub7/sS2A2ha8 XvRM1oEoeNmeTkGMMUkOEYfTRHD54mUqDkIXCp5kwbgwMfmkHUrjo8t+YLHg6rYj1CU3 cb2eQ2eXdHfUIspDXJq2FxLZSFNDh7StluUqdD/fAAkU8uOC4iGps+9S1KoBaQqPk/Y0 5gWQ== X-Forwarded-Encrypted: i=1; AJvYcCU79S5DnH2FCqUmU6BCLkS5VAZTaiKS6yQNjWtzW9NT0M29eAGFyhb0x0jbLlyVZ7oQ1aLHHr0eyM0G+8UF@lists.postgresql.org X-Gm-Message-State: AOJu0YyYlQ9hAaYnWzclidapKG56/WFnzSLrclN6fM/EMDWhmlwyfCF6 vhD4aa5dofDwKrhQft+O7RlaZyKrhbre+pKgG1eBBITIQRkqk6ma4xBXj99mBKsJILQ7l/o4xRO uJJodwbDGnlfKacOlcMoOA3gcugNo5gg= X-Gm-Gg: ASbGncu20dzf5xj1k/dk4my/4LT35TWTfQmnX6UJUrBceLRo53KecczhBu1OkOSybPx jgFRPeYv/jtWeppQdXCFC0W8Biotw0me+MNcsnwvXX+R5WBxpRp6pkpeAhpggU9iFt5H3O7OCtu sQTgv6NKTvKWnQFr95vMD6AxuBMaa5AOUXBCMjdlTWR9qY2CMiH2idklJez+5XFcjh2UF9KIHSG mMygOVr X-Google-Smtp-Source: AGHT+IGW1ZW+YPLpJjJLGcjTCb/hDlKnJrEbhDpE6Rmy2aFevf0mREf+Qk68u68r+JZq+NmqcFczikdwFX7fflOScB0= X-Received: by 2002:ac2:4f03:0:b0:55b:574c:6c06 with SMTP id 2adb3069b0e04-55b5f4c61c5mr908293e87.51.1753463253602; Fri, 25 Jul 2025 10:07:33 -0700 (PDT) MIME-Version: 1.0 References: <1934172.1753462470@sss.pgh.pa.us> In-Reply-To: From: Rumpi Gravenstein Date: Fri, 25 Jul 2025 13:07:20 -0400 X-Gm-Features: Ac12FXx9Q2ayyoD8Db__dhfuUuloPamy0sn8NLV2iS5i020aMpqJfcQqvjYHjJY Message-ID: Subject: Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array To: Adrian Klaver Cc: Tom Lane , PostgreSQL Content-Type: multipart/alternative; boundary="0000000000005b964d063ac3fa39" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005b964d063ac3fa39 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable There is really only one function with this name. A rerun of my test script with the suggested change: xxxx_pub_dev_2_db=3D# SELECT version(); version ---------------------------------------------------------------------------= ------------------------------ PostgreSQL 16.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26), 64-bit (1 row) 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) xxxx_pub_dev_2_db=3D# xxxx_pub_dev_2_db=3D# On Fri, Jul 25, 2025 at 1:02=E2=80=AFPM Adrian Klaver wrote: > On 7/25/25 09:59, Rumpi Gravenstein wrote: > > No ... just one version: > > > > > > > xxxx_pub_dev_2_db=3D# \df _sa_setup_role > > Do: > > \df *._sa_setup_role > > > > > -- > > Rumpi Gravenstein > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > --=20 Rumpi Gravenstein --0000000000005b964d063ac3fa39 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
There is really only one function with this name.=C2=A0 A = rerun of my test script with the suggested change:

xxxx_pub_dev_2_db= =3D# SELECT version();
=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 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0version
------------= ---------------------------------------------------------------------------= ------------------
=C2=A0PostgreSQL 16.9 on x86_64-pc-linux-gnu, compile= d by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26), 64-bit
(1 row)

=
xxxx_pub_dev_2_db=3D# SHOW server_version;
=C2=A0server_version
-= ---------------
=C2=A016.9
(1 row)


xxxx_pub_dev_2_db=3D# D= ROP FUNCTION if exists _sa_setup_role;
DROP FUNCTION
xxxx_pub_dev_2_d= b=3D# CREATE OR REPLACE FUNCTION _sa_setup_role( p_role_to_be_granted varch= ar)
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= $# =C2=A0 return('Done');
xxxx_pub_dev_2_db$# end;
xxxx_pub_d= ev_2_db$# $function$;
CREATE FUNCTION
xxxx_pub_dev_2_db=3D# select _s= a_setup_role('af_repo_app');
ERROR: =C2=A0malformed array litera= l: "af_repo_app"
LINE 1: select _sa_setup_role('af_repo_ap= p');
=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 ^
DETAIL: =C2=A0Array value mu= st start with "{" or dimension information.
xxxx_pub_dev_2_db= =3D# select _sa_setup_role('af_repo_app'::varchar);
INFO: =C2=A0= af_repo_app
=C2=A0_sa_setup_role
----------------
=C2=A0Done
(1= row)


xxxx_pub_dev_2_db=3D# \df *_sa_setup_role
=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
=C2=A0Sc= hema | =C2=A0 =C2=A0 =C2=A0Name =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
--------+----------------+------------------= -+----------------------------------------+------
=C2=A0sqlapp | _sa_set= up_role | character varying | p_role_to_be_granted character varying | func=
(1 row)


xxxx_pub_dev_2_db=3D#
xxxx_pub_dev_2_db=3D#
=
On Fri, Jul 25, 2025 at 1:02=E2=80=AFPM Adrian Klaver <<= a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com&g= t; wrote:
On 7/2= 5/25 09:59, Rumpi Gravenstein wrote:
> No ... just one version:
>

>
> xxxx_pub_dev_2_db=3D# \df _sa_setup_role

Do:

\df *._sa_setup_role

>
> --
> Rumpi Gravenstein


--
Adrian Klaver
adrian.klave= r@aklaver.com


--
Rumpi Gravenstein
--0000000000005b964d063ac3fa39--