Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

Hi BharatDB,

On Tue, Nov 25, 2025 at 5:15 PM BharatDB <bharatdbpg(at)gmail(dot)com> wrote:

> Hi Ian,
>
> +1 for the patch,LGTM
>
> But after applying the patch i can be able to apply all the results
> except the reset options user variables
>
> postgres=# alter role bob in DATABASE postgres reset ALL
>
> also i cross verified that my system doesn't have variables so it
> returns 0 rows?but:
>
> postgres=# SELECT name FROM pg_settings LIMIT 5;
> name
> ----------------------------
> allow_alter_system
> allow_in_place_tablespaces
> allow_system_table_mods
> application_name
> archive_cleanup_command
> (5 rows)
>
>
The tab-completion here queries the user-specific config (not the global
pg_settings). I believe the underlying code logic will help explain this
behavior:

> + else if (TailMatches("DATABASE", MatchAny, "RESET"))
> + {
> + set_completion_reference(prev5_wd);
> + COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_user_vars, "ALL");
> + }

#define Query_for_list_of_user_vars \
> "SELECT conf FROM ("\
> " SELECT rolname,
> pg_catalog.split_part(pg_catalog.unnest(rolconfig),'=',1) conf"\
> " FROM pg_catalog.pg_roles"\
> " ) s"\
> " WHERE s.conf like '%s' "\
> " AND s.rolname LIKE '%s'"