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

Ian Lawrence Barwick <barwick(at)gmail(dot)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