On Thu, Feb 1, 2024 at 4:34 PM Greg Sabino Mullane <htamf...@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
> gather all of the information it needs. Unfortunately, those queries can get
> overwhelming and hard to figure out which one does what, especially for those
> not already very familiar with the system catalogs. Attached is a patch to
> add a small SQL comment to the top of each SELECT query inside
> describeOneTableDetail. All other functions use a single query, and thus need
> no additional context. But "\d mytable" has the potential to run over a dozen
> SQL queries! The new format looks like 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_roles
> where 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,
> GregThanks, 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
0001-Add-output-of-the-command-that-got-us-here-to-the-QU.patch
Description: Binary data
