Hi Greg, hi David
On 01.02.24 23:39, David Christensen wrote:
> 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,
>> 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
Having this kind of information in each query would have saved me a lot
of time in the past :) +1
There is a tiny little issue in the last patch (qualifiers):
command.c:312:16: warning: assignment discards ‘const’ qualifier from
pointer target type [-Wdiscarded-qualifiers]
312 | curcmd = cmd;
Thanks
--
Jim
