[PATCH] psql: add size-based sorting options (O/o) for tables and indexes - Mailing list pgsql-hackers

This patch adds two new meta-command modifiers for \dt(+) and \di(+):

  - O  : sort by total relation size descending
  - o  : sort by total relation size ascending

This makes it easier to identify the largest tables and indexes
without writing custom SQL queries.

Help message (\?) is updated to reflect the new options.

Changes:

 src/bin/psql/describe.c | 24 +++++++++++++++++++++++-
 src/bin/psql/help.c     |  4 ++--
 2 files changed, 25 insertions(+), 3 deletions(-)

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 36f24502842..33eb5e799c7 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4194,7 +4194,29 @@ listTables(const char *tabtypes, const char
*pattern, bool verbose, bool showSys
  return false;
  }

- appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ if (showTables || showIndexes)
+ {
+ if (strchr(tabtypes, 'o') != NULL)
+ appendPQExpBufferStr(&buf, "ORDER BY pg_catalog.pg_table_size(c.oid), 1, 2;");
+ else if (strchr(tabtypes, 'O') != NULL)
+ appendPQExpBufferStr(&buf, "ORDER BY pg_catalog.pg_table_size(c.oid)
DESC, 1, 2;");
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ }
+ else
+ {
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ }

  res = PSQLexec(buf.data);
  termPQExpBuffer(&buf);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ec0b49b957b..f58b66b2aa0 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -246,7 +246,7 @@ slashUsage(unsigned short int pager)
  HELP0("  \\di[Sx+] [PATTERN]     list indexes\n");
+ HELP0("  \\di[OoSx+] [PATTERN]   list indexes\n");
@@ -262,7 +262,7 @@ slashUsage(unsigned short int pager)
- HELP0("  \\dt[Sx+] [PATTERN]     list tables\n");
+ HELP0("  \\dt[OoSx+] [PATTERN]   list tables\n");

-- 
2.39.5 (Apple Git-154)