Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed

On 28 October 2017 at 13:46, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> I though about Alexander proposal, and I am thinking so it can be probably
> best if we respect psql design. I implemented two command suffixes
> (supported only when it has sense) "s" sorted by size and "d" as descent
>
> so list of tables can be sorted with commands:
>
> \dt+sd (in this case, the order is not strict), so command
> \dtsd+ is working too (same \disd+ or \di+sd)
>
> These two chars are acceptable. Same principle is used for \l command
>
> \lsd+ or \l+sd
>
> What do you think about it?
>

I really hate that syntax. This is going to turn into an
incomprehensible mess, and isn't easily extended to support other
options.

I agree with people who have said they would prefer this to be
available as a per-command option rather than as a variable that you
have to set, but it needs a clearer syntax. I actually like Stephen's
idea of using a user-defined SQL snippet, because that's a familiar
syntax to people, and it avoids adding an ever-increasing number of
options to these commands. Instead, the syntax could simply be:

\d[S+] [ pattern ] [ ( auxiliary sql ) ]

(and similar for the other commands)

The auxiliary SQL could be pretty much anything to allow user-defined
ordering and filtering.

I think parsing the optional auxiliary SQL snippet in parentheses at
the end should be quite straightforward, provided that psql makes no
attempt to actually parse the SQL contained in the parentheses -- it
should just add it to the SQL it sends to the backend (like \copy
does). For example, for \d+, instead of generating

SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN
'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN
'p' THEN 'table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

we could generate

SELECT schema as "Schema",
name as "Name",
type as "Type",
owner as "Owner",
pg_catalog.pg_size_pretty(size) as "Size",
description as "Description"
FROM (
SELECT n.nspname as schema,
c.relname as name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN
'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN
'p' THEN 'table' END as type,
pg_catalog.pg_get_userbyid(c.relowner) as owner,
pg_catalog.pg_table_size(c.oid) as size,
pg_catalog.obj_description(c.oid, 'pg_class') as description
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2
) as t
<auxiliary sql>;

That would allow things like

\d+ (order by size)

which would sort by the numeric size, while displaying the pretty size
in the output.

This would also allow more complex orderings that would be hard to
achieve any other way, such as

\d+ (order by type, schema, size desc)
\dt (order by pg_total_relation_size(name::regclass))

(note the size reported by \d+ is not the total relation size, because
it excludes indexes)

Also, it would allow user-defined WHERE clauses to filter the results
shown, for example:

\d+ (where size > pg_size_bytes('1GB'))
\dv (where pg_relation_is_updatable(name::regclass, true) != 0)

and many more things are possible, without needing to learn any new
syntax, and without needing to keep adding more and more options to
the psql syntax.

Regards,
Dean