Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ... - Mailing list pgsql-hackers

Ian Lawrence Barwick <barwick@gmail.com> writes:

> Hi
>
> I found myself needing to work with ALTER ROLE ... IN DATABASE ... recently
> and was annoyed by the lack of tab completion for this, so patch attached.

A noble goal, but unfortunately th RESET form can't work properly due to
limitations of the tab completion system.

> +    /* ALTER USER,ROLE <name> IN DATABASE */
> +    else if (HeadMatches("ALTER", "USER|ROLE", MatchAny, "IN"))
> +    {
[...]
> +        else if (TailMatches("DATABASE", MatchAny, "RESET"))
> +        {
> +            set_completion_reference(prev5_wd);
> +            COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_user_vars, "ALL");

This queries pg_roles.rolconfig, which only contains variables set for
the user in all databases, not in the specified database.  Instead,
you'd need to query pg_db_role_setting WHERE setdatabase = (SELECT oid
FROM pg_database WHERE datname = '%s') AND setrole = '%s'::regrole, but
unfortunately the tab completion system doesn't let you more than one
previous word in the query.  I guess you could query WHERE setdatabase
<> 0, to get variables set for the user across all databases, not just
the specified one.

Also, alter ALTER ROLE ALL RESET needs separate handling, filtering
where setrole = 0, which is actually possible in the current system.

- ilmari