Hi
In general, this approach looks good for me.
> Regarding current state of patch, I'd like to see new options documented.
> Also, it would be better to replace "bool sort_size" with enum assuming
> there could be other sorting orders in future.
>
I am sending updated patch with some basic doc
Regards
Pavel
>
> ------
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index e520cdf3ba..7d816fe701 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1303,10 +1303,10 @@ testdb=>
<varlistentry>
<term><literal>\dE[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
- <term><literal>\di[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
- <term><literal>\dm[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\di[Ssd+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\dm[Ssd+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\ds[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
- <term><literal>\dt[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\dt[Ssd+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<term><literal>\dv[S+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
@@ -1328,6 +1328,13 @@ testdb=>
pattern or the <literal>S</literal> modifier to include system
objects.
</para>
+
+ <para>
+ When command contains <literal>s</literal>, then a result is
+ sorted by size. When command contains <literal>d</literal> then
+ result is in descend order. <literal>\dtsd+</literal> shows list
+ of tables sorted by size with descend order.
+ </para>
</listitem>
</varlistentry>
@@ -2253,7 +2260,7 @@ SELECT
<varlistentry>
- <term><literal>\l[+]</literal> or <literal>\list[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\l[sd+]</literal> or <literal>\list[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
List the databases in the server and show their names, owners,
@@ -2265,6 +2272,12 @@ SELECT
(Size information is only available for databases that the current
user can connect to.)
</para>
+
+ <para>
+ If <literal>s</literal> is used in command name, then the list is
+ sorted by size. When <literal>d</literal> is used there, then result
+ is in descend order.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 041b5e0c87..aae88b08b4 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -349,8 +349,9 @@ exec_command(const char *cmd,
status = exec_command_include(scan_state, active_branch, cmd);
else if (strcmp(cmd, "if") == 0)
status = exec_command_if(scan_state, cstack, query_buf);
- else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
- strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
+ else if (strcmp(cmd, "list") == 0 || strcmp(cmd, "list+") == 0 ||
+ strcmp(cmd, "l") == 0 || strncmp(cmd, "l+", 2) == 0 ||
+ strncmp(cmd, "ls", 2) == 0)
status = exec_command_list(scan_state, active_branch, cmd);
else if (strncmp(cmd, "lo_", 3) == 0)
status = exec_command_lo(scan_state, active_branch, cmd);
@@ -702,7 +703,9 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
{
char *pattern;
bool show_verbose,
- show_system;
+ show_system,
+ sort_desc;
+ sortby_type sortby;
/* We don't do SQLID reduction on the pattern yet */
pattern = psql_scan_slash_option(scan_state,
@@ -711,6 +714,9 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
show_verbose = strchr(cmd, '+') ? true : false;
show_system = strchr(cmd, 'S') ? true : false;
+ sortby = SORTBY_SCHEMA_NAME;
+ sort_desc = false;
+
switch (cmd[1])
{
case '\0':
@@ -720,7 +726,8 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = describeTableDetails(pattern, show_verbose, show_system);
else
/* standard listing of interesting things */
- success = listTables("tvmsE", NULL, show_verbose, show_system);
+ success = listTables("tvmsE", NULL, show_verbose, show_system,
+ false, false);
break;
case 'A':
success = describeAccessMethods(pattern, show_verbose);
@@ -789,12 +796,20 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = describeTypes(pattern, show_verbose, show_system);
break;
case 't':
- case 'v':
case 'm':
case 'i':
+ if (strlen(cmd) >= 2)
+ {
+ if (strchr(&cmd[2], 's') != NULL)
+ sortby = SORTBY_SIZE;
+ sort_desc = strchr(&cmd[2], 'd') ? true : false;
+ }
+
+ case 'v':
case 's':
case 'E':
- success = listTables(&cmd[1], pattern, show_verbose, show_system);
+ success = listTables(&cmd[1], pattern, show_verbose, show_system,
+ sortby, sort_desc);
break;
case 'r':
if (cmd[2] == 'd' && cmd[3] == 's')
@@ -1655,13 +1670,17 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
{
char *pattern;
bool show_verbose;
+ bool sort_desc;
+ sortby_type sortby;
pattern = psql_scan_slash_option(scan_state,
OT_NORMAL, NULL, true);
show_verbose = strchr(cmd, '+') ? true : false;
+ sortby = strchr(cmd, 's') != NULL ? SORTBY_SIZE : SORTBY_NAME;
+ sort_desc = strchr(cmd, 'd') ? true : false;
- success = listAllDbs(pattern, show_verbose);
+ success = listAllDbs(pattern, show_verbose, sortby, sort_desc);
if (pattern)
free(pattern);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 986172616e..8513022e09 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -782,7 +782,7 @@ describeOperators(const char *pattern, bool verbose, bool showSystem)
* for \l, \list, and -l switch
*/
bool
-listAllDbs(const char *pattern, bool verbose)
+listAllDbs(const char *pattern, bool verbose, sortby_type sortby, bool sort_desc)
{
PGresult *res;
PQExpBufferData buf;
@@ -830,7 +830,30 @@ listAllDbs(const char *pattern, bool verbose)
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "d.datname", NULL, NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ if (sortby == SORTBY_SIZE)
+ {
+ if (pset.sversion < 80200)
+ {
+ char sverbuf[32];
+
+ psql_error("The server (version %s) does not support database size function.\n",
+ formatPGVersionNumber(pset.sversion, false,
+ sverbuf, sizeof(sverbuf)));
+ return true;
+ }
+
+ appendPQExpBufferStr(&buf, "ORDER BY pg_catalog.pg_database_size(d.datname)");
+
+ if (sort_desc)
+ appendPQExpBuffer(&buf, " DESC");
+
+ appendPQExpBuffer(&buf, ", 1;");
+ }
+ else
+ {
+ Assert(sortby == SORTBY_NAME);
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ }
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
if (!res)
@@ -3336,7 +3359,8 @@ listDbRoleSettings(const char *pattern, const char *pattern2)
* (any order of the above is fine)
*/
bool
-listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
+listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem,
+ sortby_type sortby, bool sort_desc)
{
bool showTables = strchr(tabtypes, 't') != NULL;
bool showIndexes = strchr(tabtypes, 'i') != NULL;
@@ -3457,7 +3481,37 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
"n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
- appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ if (sortby == SORTBY_SIZE)
+ {
+ appendPQExpBufferStr(&buf, "ORDER BY ");
+
+ /*
+ * As of PostgreSQL 9.0, use pg_table_size() to show a more accurate
+ * size of a table, including FSM, VM and TOAST tables.
+ */
+ if (pset.sversion >= 90000)
+ appendPQExpBuffer(&buf, "pg_catalog.pg_table_size(c.oid)");
+ else if (pset.sversion >= 80100)
+ appendPQExpBuffer(&buf, "pg_catalog.pg_relation_size(c.oid)");
+ else
+ {
+ char sverbuf[32];
+
+ psql_error("The server (version %s) does not support table size function.\n",
+ formatPGVersionNumber(pset.sversion, false,
+ sverbuf, sizeof(sverbuf)));
+ return true;
+ }
+
+ if (sort_desc)
+ appendPQExpBufferStr(&buf, "DESC");
+ appendPQExpBufferStr(&buf, " , 1,2;");
+ }
+ else
+ {
+ Assert(sortby == SORTBY_SCHEMA_NAME);
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ }
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 14a5667f3e..37b812830d 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -8,6 +8,13 @@
#ifndef DESCRIBE_H
#define DESCRIBE_H
+/* how the result list should be sorted */
+typedef enum sortby_type
+{
+ SORTBY_NAME,
+ SORTBY_SCHEMA_NAME,
+ SORTBY_SIZE
+} sortby_type;
/* \da */
extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
@@ -58,10 +65,11 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
extern bool listTSTemplates(const char *pattern, bool verbose);
/* \l */
-extern bool listAllDbs(const char *pattern, bool verbose);
+extern bool listAllDbs(const char *pattern, bool verbose, sortby_type sortby, bool sort_desc);
/* \dt, \di, \ds, \dS, etc. */
-extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
+extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem,
+ sortby_type sortby, bool sort_desc);
/* \dD */
extern bool listDomains(const char *pattern, bool verbose, bool showSystem);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index a926c40b9b..620b8930a5 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -241,10 +241,10 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dFp[+] [PATTERN] list text search parsers\n"));
fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n"));
fprintf(output, _(" \\dg[S+] [PATTERN] list roles\n"));
- fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
+ fprintf(output, _(" \\di[Ssd+] [PATTERN] list indexes\n"));
fprintf(output, _(" \\dl list large objects, same as \\lo_list\n"));
fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n"));
- fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n"));
+ fprintf(output, _(" \\dm[Ssd+] [PATTERN] list materialized views\n"));
fprintf(output, _(" \\dn[S+] [PATTERN] list schemas\n"));
fprintf(output, _(" \\do[S] [PATTERN] list operators\n"));
fprintf(output, _(" \\dO[S+] [PATTERN] list collations\n"));
@@ -253,13 +253,13 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dRp[+] [PATTERN] list replication publications\n"));
fprintf(output, _(" \\dRs[+] [PATTERN] list replication subscriptions\n"));
fprintf(output, _(" \\ds[S+] [PATTERN] list sequences\n"));
- fprintf(output, _(" \\dt[S+] [PATTERN] list tables\n"));
+ fprintf(output, _(" \\dt[Ssd+] [PATTERN] list tables\n"));
fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
fprintf(output, _(" \\du[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
fprintf(output, _(" \\dx[+] [PATTERN] list extensions\n"));
fprintf(output, _(" \\dy [PATTERN] list event triggers\n"));
- fprintf(output, _(" \\l[+] [PATTERN] list databases\n"));
+ fprintf(output, _(" \\l[sd+] [PATTERN] list databases\n"));
fprintf(output, _(" \\sf[+] FUNCNAME show a function's definition\n"));
fprintf(output, _(" \\sv[+] VIEWNAME show a view's definition\n"));
fprintf(output, _(" \\z [PATTERN] same as \\dp\n"));
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
