Re: Adding comments to help understand psql hidden queries

On Thu, Feb 1, 2024 at 4:34 PM Greg Sabino Mullane <htamfids@gmail.com> wrote:
>
> The use of the --echo-hidden flag in psql is used to show people the way psql performs its magic for its backslash
commands.None of them has more magic than "\d relation", but it suffers from needing a lot of separate queries to
gatherall of the information it needs. Unfortunately, those queries can get overwhelming and hard to figure out which
onedoes what, especially for those not already very familiar with the system catalogs. Attached is a patch to add a
smallSQL comment to the top of each SELECT query inside describeOneTableDetail. All other functions use a single query,
andthus need no additional context. But "\d mytable" has the potential to run over a dozen SQL queries! The new format
lookslike this: 
>
> /******** QUERY *********/
> /* Get information about row-level policies */
> SELECT pol.polname, pol.polpermissive,
>   CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(array(select rolname from
pg_catalog.pg_roleswhere oid = any (pol.polroles) order by 1),',') END, 
>   pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),
>   pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),
>   CASE pol.polcmd
>     WHEN 'r' THEN 'SELECT'
>     WHEN 'a' THEN 'INSERT'
>     WHEN 'w' THEN 'UPDATE'
>     WHEN 'd' THEN 'DELETE'
>     END AS cmd
> FROM pg_catalog.pg_policy pol
> WHERE pol.polrelid = '134384' ORDER BY 1;
> /************************/
>
> Cheers,
> Greg

Thanks, this looks like some helpful information. In the same vein,
I'm including a patch which adds information about the command that
generates the given query as well (atop your commit).  This will
modify the query line to include the command itself:

/******** QUERY (\dRs) *********/

Best,

David