Hi
2017-02-23 12:17 GMT+01:00 Pavel Stehule <[email protected]>:
> Hi
>
> Currently is not possible to control sort columns for \d* commands.
> Usually schema and table name is used. Really often task is collect the
> most big objects in database. "\dt+, \di+" shows necessary information, but
> not in practical order.
>
> Instead introduction some additional flags to backslash commands, I
> propose a special psql variable that can be used for specification of order
> used when some plus command is used.
>
> some like
>
> set EXTENDED_DESCRIBE_SORT size_desc
> \dt+
> \l+
> \di+
>
> Possible variants: schema_table, table_schema, size_desc, size_asc
>
> Comments, notes?
>
here is a patch
Regards
Pavel
>
> Regards
>
> Pavel
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index ae58708aae..b4dfd1f71c 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3495,6 +3495,18 @@ bar
</varlistentry>
<varlistentry>
+ <term><varname>VERBOSE_SORT</varname></term>
+ <listitem>
+ <para>
+ This variable can be set to the values <literal>schema_name</>,
+ <literal>name_schema</>, <literal>size_asc</literal>, or
+ <literal>size_desc</> to control the order of content of
+ decrible command.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><varname>VERBOSITY</varname></term>
<listitem>
<para>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e2e4cbcc08..7ae5992b90 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -263,7 +263,18 @@ describeTablespaces(const char *pattern, bool verbose)
NULL, "spcname", NULL,
NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+
+ if (verbose && pset.sversion >= 90200)
+ {
+ if (pset.verbose_sort == PSQL_SORT_SIZE_ASC)
+ appendPQExpBufferStr(&buf, "ORDER BY pg_catalog.pg_tablespace_size(oid), 1;");
+ else if (pset.verbose_sort == PSQL_SORT_SIZE_DESC)
+ appendPQExpBufferStr(&buf, "ORDER BY pg_catalog.pg_tablespace_size(oid) DESC, 1;");
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
@@ -822,7 +833,21 @@ listAllDbs(const char *pattern, bool verbose)
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "d.datname", NULL, NULL);
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ if (verbose && pset.sversion >= 80200)
+ {
+ if (pset.verbose_sort == PSQL_SORT_SIZE_ASC)
+ appendPQExpBuffer(&buf,
+ "ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
+ " THEN pg_catalog.pg_database_size(d.datname) END ASC, 1;\n");
+ else if (pset.verbose_sort == PSQL_SORT_SIZE_DESC)
+ appendPQExpBuffer(&buf,
+ "ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
+ " THEN pg_catalog.pg_database_size(d.datname) END DESC, 1;\n");
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
if (!res)
@@ -3258,7 +3283,29 @@ 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 (verbose && pset.sversion >= 80100)
+ {
+ if (pset.verbose_sort == PSQL_SORT_SCHEMA_NAME)
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ else if (pset.verbose_sort == PSQL_SORT_NAME_SCHEMA)
+ appendPQExpBufferStr(&buf, "ORDER BY 2,1;");
+ else
+ {
+ if (pset.sversion >= 90000)
+ appendPQExpBufferStr(&buf,
+ "ORDER BY pg_catalog.pg_table_size(c.oid) ");
+ else
+ appendPQExpBufferStr(&buf,
+ "ORDER BY pg_catalog.pg_relation_size(c.oid) ");
+
+ if (pset.verbose_sort == PSQL_SORT_SIZE_DESC)
+ appendPQExpBufferStr(&buf, "DESC, 1,2;");
+ else
+ appendPQExpBufferStr(&buf, "ASC, 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 3e3cab4941..09c1a49413 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -327,7 +327,7 @@ helpVariables(unsigned short int pager)
* Windows builds currently print one more line than non-Windows builds.
* Using the larger number is fine.
*/
- output = PageOutput(88, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(90, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("List of specially treated variables\n\n"));
@@ -364,6 +364,8 @@ helpVariables(unsigned short int pager)
fprintf(output, _(" SINGLESTEP single-step mode (same as -s option)\n"));
fprintf(output, _(" USER the currently connected database user\n"));
fprintf(output, _(" VERBOSITY controls verbosity of error reports [default, verbose, terse]\n"));
+ fprintf(output, _(" VERBOSE_SORT controls sort of result in verbose mode\n"
+ " [schema_name, name_schema, size_desc, size_asc]\n"));
fprintf(output, _("\nDisplay settings:\n"));
fprintf(output, _("Usage:\n"));
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 195f5a1184..eb89741526 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -77,6 +77,14 @@ enum trivalue
TRI_YES
};
+typedef enum
+{
+ PSQL_SORT_SCHEMA_NAME,
+ PSQL_SORT_NAME_SCHEMA,
+ PSQL_SORT_SIZE_ASC,
+ PSQL_SORT_SIZE_DESC
+} PSQL_SORT;
+
typedef struct _psqlSettings
{
PGconn *db; /* connection to backend */
@@ -137,6 +145,7 @@ typedef struct _psqlSettings
const char *prompt3;
PGVerbosity verbosity; /* current error verbosity level */
PGContextVisibility show_context; /* current context display level */
+ PSQL_SORT verbose_sort; /* kind of sort when describe verbose is used */
} PsqlSettings;
extern PsqlSettings pset;
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 88d686a5b7..8971e927d5 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -172,6 +172,8 @@ main(int argc, char *argv[])
SetVariable(pset.vars, "PROMPT2", DEFAULT_PROMPT2);
SetVariable(pset.vars, "PROMPT3", DEFAULT_PROMPT3);
+ SetVariable(pset.vars, "VERBOSE_SORT", "schema_name");
+
parse_psql_options(argc, argv, &options);
/*
@@ -1072,6 +1074,36 @@ verbosity_hook(const char *newval)
}
static char *
+verbose_sort_substitute_hook(char *newval)
+{
+ if (newval == NULL)
+ newval = pg_strdup("schema_name");
+ return newval;
+}
+
+static bool
+verbose_sort_hook(const char *newval)
+{
+ Assert(newval != NULL); /* else substitute hook messed up */
+ if (pg_strcasecmp(newval, "schema_name") == 0)
+ pset.verbose_sort = PSQL_SORT_SCHEMA_NAME;
+ else if (pg_strcasecmp(newval, "name_schema") == 0)
+ pset.verbose_sort = PSQL_SORT_NAME_SCHEMA;
+ else if (pg_strcasecmp(newval, "size_desc") == 0)
+ pset.verbose_sort = PSQL_SORT_SIZE_DESC;
+ else if (pg_strcasecmp(newval, "size_asc") == 0)
+ pset.verbose_sort = PSQL_SORT_SIZE_ASC;
+ else
+ {
+ PsqlVarEnumError("VERBOSE_SORT", newval, "schema_name, name_schema, size_desc, size_asc");
+ return false;
+ }
+
+ return true;
+}
+
+
+static char *
show_context_substitute_hook(char *newval)
{
if (newval == NULL)
@@ -1160,6 +1192,9 @@ EstablishVariableSpace(void)
SetVariableHooks(pset.vars, "VERBOSITY",
verbosity_substitute_hook,
verbosity_hook);
+ SetVariableHooks(pset.vars, "VERBOSE_SORT",
+ verbose_sort_substitute_hook,
+ verbose_sort_hook);
SetVariableHooks(pset.vars, "SHOW_CONTEXT",
show_context_substitute_hook,
show_context_hook);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 94814c20d0..2bc8af5ae4 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3320,6 +3320,9 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_LIST_CS3("never", "errors", "always");
else if (TailMatchesCS1("VERBOSITY"))
COMPLETE_WITH_LIST_CS3("default", "verbose", "terse");
+ else if (TailMatchesCS1("VERBOSE_SORT"))
+ COMPLETE_WITH_LIST_CS4("schema_name", "name_schema",
+ "size_desc", "size_asc");
}
else if (TailMatchesCS1("\\sf*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
