2017-08-16 14:06 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:
> Hi
>
> 2017-08-15 4:37 GMT+02:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.
> com>:
>
>> On 3/11/17 07:06, Pavel Stehule wrote:
>> > I am sending a updated version with separated sort direction in special
>> > variable
>>
>> This patch also needs a rebase.
>>
>
> I am sending rebased patch
>
rebased again + fix obsolete help
Regards
Pavel
>
> Regards
>
> Pavel
>
>
>>
>> --
>> Peter Eisentraut http://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 79468a5663..d51c4bf900 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3796,6 +3796,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 6fb9bdd063..3ead55856d 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -200,6 +200,8 @@ 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
@@ -268,7 +270,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);
@@ -830,7 +843,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)
@@ -3424,7 +3449,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 4d1c0ec3c6..a28fe07aa2 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -337,7 +337,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(147, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(151, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("List of specially treated variables\n\n"));
@@ -401,6 +401,10 @@ helpVariables(unsigned short int pager)
" the currently connected database user\n"));
fprintf(output, _(" VERBOSITY\n"
" controls verbosity of error reports [default, verbose, terse]\n"));
+ fprintf(output, _(" VERBOSE_SORT_COLUMNS\n"
+ " controls sort of result in verbose mode [schema_name, name_schema, size]\n"));
+ fprintf(output, _(" VERBOSE_SORT_DIRECTION\n"
+ " controls direction of order of result in verbose mode [asc, desc]\n"));
fprintf(output, _(" VERSION\n"
" VERSION_NAME\n"
" VERSION_NUM\n"
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 96338c3197..5a5fa0adac 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 */
@@ -139,6 +152,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 1e48f4ad5a..f03dcc62cd 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -171,6 +171,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);
/*
@@ -1078,6 +1081,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)
@@ -1166,6 +1223,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 2ab8809fa5..4c0dab5ec8 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3567,6 +3567,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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
