2017-03-10 15:45 GMT+01:00 Alexander Korotkov <[email protected]>:
> On Fri, Mar 10, 2017 at 5:10 PM, Peter Eisentraut <
> [email protected]> wrote:
>
>> On 2/24/17 16:32, Pavel Stehule wrote:
>> > set EXTENDED_DESCRIBE_SORT size_desc
>> > \dt+
>> > \l+
>> > \di+
>> >
>> > Possible variants: schema_table, table_schema, size_desc, size_asc
>>
>> I can see this being useful, but I think it needs to be organized a
>> little better.
>>
>> Sort key and sort direction should be separate settings.
>>
>
> I agree.
>
> I'm not sure why we need to have separate settings to sort by schema
>> name and table name.
>
>
> I think sorting by schema name, object name makes sense for people, who
> have objects of same name in different schemas.
>
I am sending a updated version with separated sort direction in special
variable
There is a question. Has desc direction sense for columns like schema or
table name?
Using desc, asc for size is natural. But for tablename?
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 2a9c412020..747db58dd8 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3507,6 +3507,27 @@ bar
</varlistentry>
<varlistentry>
+ <term><varname>VERBOSE_SORT_COLUMNS</varname></term>
+ <listitem>
+ <para>
+ This variable can be set to the values <literal>schema_name</>,
+ <literal>name_schema</> or <literal>size</> to control the
+ order of content of decrible command.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>VERBOSE_SORT_DIRECTION</varname></term>
+ <listitem>
+ <para>
+ This variable can be set to the values <literal>asc</>,
+ or <literal>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 61a3e2a848..7ba24ea883 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -197,6 +197,9 @@ describeAccessMethods(const char *pattern, bool verbose)
return true;
}
+#define SORT_DIRECTION_STR(v) ((v) == PSQL_SORT_ASC ? "ASC" : "DESC")
+
+
/* \db
* Takes an optional regexp to select particular tablespaces
*/
@@ -264,7 +267,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_columns == PSQL_SORT_SIZE)
+ appendPQExpBuffer(&buf,
+ "ORDER BY pg_catalog.pg_tablespace_size(oid) %s, 1;",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
@@ -824,7 +838,19 @@ 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_columns == PSQL_SORT_SIZE)
+ appendPQExpBuffer(&buf,
+ "ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
+ " THEN pg_catalog.pg_database_size(d.datname) END %s, 1;\n",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1");
+ }
+ else
+ appendPQExpBufferStr(&buf, "ORDER BY 1");
+
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
if (!res)
@@ -3295,7 +3321,26 @@ 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_columns == PSQL_SORT_SCHEMA_NAME)
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
+ else if (pset.verbose_sort_columns == PSQL_SORT_NAME_SCHEMA)
+ appendPQExpBufferStr(&buf, "ORDER BY 2,1;");
+ else
+ {
+ if (pset.sversion >= 90000)
+ appendPQExpBuffer(&buf,
+ "ORDER BY pg_catalog.pg_table_size(c.oid) %s, 1,2",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ else
+ appendPQExpBuffer(&buf,
+ "ORDER BY pg_catalog.pg_relation_size(c.oid) %s, 1,2",
+ SORT_DIRECTION_STR(pset.verbose_sort_direction));
+ }
+ }
+ 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 ba14df0344..1ebe397a85 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(92, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("List of specially treated variables\n\n"));
@@ -364,6 +364,10 @@ 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_COLUMNS\n"
+ " sort columns for verbose mode [schema_name, name_schema, size]\n"));
+ fprintf(output, _(" VERBOSE_SORT_DIRECTION\n"
+ " direction of sort of verbose mode [asc, desc]\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 70ff1812c8..0f2af11d89 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -77,6 +77,19 @@ enum trivalue
TRI_YES
};
+typedef enum
+{
+ PSQL_SORT_SCHEMA_NAME,
+ PSQL_SORT_NAME_SCHEMA,
+ PSQL_SORT_SIZE,
+} PSQL_SORT_COLUMNS;
+
+typedef enum
+{
+ PSQL_SORT_ASC,
+ PSQL_SORT_DESC
+} PSQL_SORT_DIRECTION;
+
typedef struct _psqlSettings
{
PGconn *db; /* connection to backend */
@@ -138,6 +151,8 @@ typedef struct _psqlSettings
const char *prompt3;
PGVerbosity verbosity; /* current error verbosity level */
PGContextVisibility show_context; /* current context display level */
+ PSQL_SORT_COLUMNS verbose_sort_columns; /* sort columns for describe verbose command */
+ PSQL_SORT_DIRECTION verbose_sort_direction; /* sort direction for describe verbose command */
} PsqlSettings;
extern PsqlSettings pset;
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 694f0ef257..c25140e33d 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -168,6 +168,9 @@ main(int argc, char *argv[])
SetVariable(pset.vars, "PROMPT2", DEFAULT_PROMPT2);
SetVariable(pset.vars, "PROMPT3", DEFAULT_PROMPT3);
+ SetVariable(pset.vars, "VERBOSE_SORT_COLUMNS", "schema_name");
+ SetVariable(pset.vars, "VERBOSE_SORT_DIRECTION", "asc");
+
parse_psql_options(argc, argv, &options);
/*
@@ -1068,6 +1071,60 @@ verbosity_hook(const char *newval)
}
static char *
+verbose_sort_columns_substitute_hook(char *newval)
+{
+ if (newval == NULL)
+ newval = pg_strdup("schema_name");
+ return newval;
+}
+
+static bool
+verbose_sort_columns_hook(const char *newval)
+{
+ Assert(newval != NULL); /* else substitute hook messed up */
+ if (pg_strcasecmp(newval, "schema_name") == 0)
+ pset.verbose_sort_columns = PSQL_SORT_SCHEMA_NAME;
+ else if (pg_strcasecmp(newval, "name_schema") == 0)
+ pset.verbose_sort_columns = PSQL_SORT_NAME_SCHEMA;
+ else if (pg_strcasecmp(newval, "size") == 0)
+ pset.verbose_sort_columns = PSQL_SORT_SIZE;
+ else
+ {
+ PsqlVarEnumError("VERBOSE_SORT_COLUMNS", newval,
+ "schema_name, name_schema, size");
+ return false;
+ }
+
+ return true;
+}
+
+static char *
+verbose_sort_direction_substitute_hook(char *newval)
+{
+ if (newval == NULL)
+ newval = pg_strdup("asc");
+ return newval;
+}
+
+static bool
+verbose_sort_direction_hook(const char *newval)
+{
+ Assert(newval != NULL); /* else substitute hook messed up */
+ if (pg_strcasecmp(newval, "asc") == 0)
+ pset.verbose_sort_direction = PSQL_SORT_ASC;
+ else if (pg_strcasecmp(newval, "desc") == 0)
+ pset.verbose_sort_direction = PSQL_SORT_DESC;
+ else
+ {
+ PsqlVarEnumError("VERBOSE_SORT_DIRECTION", newval, "asc, desc");
+ return false;
+ }
+
+ return true;
+}
+
+
+static char *
show_context_substitute_hook(char *newval)
{
if (newval == NULL)
@@ -1156,6 +1213,12 @@ EstablishVariableSpace(void)
SetVariableHooks(pset.vars, "VERBOSITY",
verbosity_substitute_hook,
verbosity_hook);
+ SetVariableHooks(pset.vars, "VERBOSE_SORT_COLUMNS",
+ verbose_sort_columns_substitute_hook,
+ verbose_sort_columns_hook);
+ SetVariableHooks(pset.vars, "VERBOSE_SORT_DIRECTION",
+ verbose_sort_direction_substitute_hook,
+ verbose_sort_direction_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 e8458e939e..a252c5fa33 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3416,6 +3416,10 @@ 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_COLUMNS"))
+ COMPLETE_WITH_LIST_CS3("schema_name", "name_schema","size");
+ else if (TailMatchesCS1("VERBOSE_SORT_DIRECTION"))
+ COMPLETE_WITH_LIST_CS2("asc", "desc");
}
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
