Re: PostgreSQL Bug with simple function unexpectedly treating varchar parameter as an array

On 7/25/25 09:36, Rumpi Gravenstein wrote:
> PostgreSQL Experts,
>
> 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.
>
> ---------------------------------------------------------------------------------------------------------
> xxxx_pub_dev_2_db=# 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=# SHOW server_version;
>  server_version
> ----------------
>  16.9
> (1 row)
>
> xxxx _pub_dev_2_db=# DROP FUNCTION if exists _sa_setup_role;
> DROP FUNCTION
> xxxx _pub_dev_2_db=# 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=# 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=#select _sa_setup_role('af_repo_app'::varchar);
> INFO:  af_repo_app
>  _sa_setup_role
> ----------------
>  Done
> (1 row)
>
> I've been able to run the same script with no issues in other PostgreSQL
> databases, just not this one.
>
> Thoughts?

You have more then on version of _sa_setup_role in this database, one of
which is looking for an array argument.

In psql do

\df *._sa_setup_role

and see what it returns.

>
> Best Regards
> --
> Rumpi Gravenstein

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com