Here is a patch for psql's \l command to accept patterns, like \d
commands do. While at it, I also added an "S" option to show system
objects and removed system objects from the default display. This might
be a bit controversial, but it's how it was decided some time ago that
the \d commands should act.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index c41593c..a9770c0 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1689,16 +1689,19 @@ <title>Meta-Commands</title>
<varlistentry>
- <term><literal>\l</literal> (or <literal>\list</literal>)</term>
- <term><literal>\l+</literal> (or <literal>\list+</literal>)</term>
+ <term><literal>\l[S+]</literal> (or <literal>\list[S+]</literal>)</term>
<listitem>
<para>
- List the names, owners, character set encodings, and access privileges
- of all the databases in the server.
+ List the databases in the server and show they names, owners, character
+ set encodings, and access privileges.
+ If <replaceable class="parameter">pattern</replaceable> is specified,
+ only databases whose names match the pattern are listed.
If <literal>+</literal> is appended to the command name, database
- sizes, default tablespaces, and descriptions are also displayed.
- (Size information is only available for databases that the current
- user can connect to.)
+ sizes, default tablespaces, and descriptions are also displayed. (Size
+ information is only available for databases that the current user can
+ connect to.) By default, only user-created databases are shown; supply
+ a pattern or the <literal>S</literal> modifier to include system
+ objects.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 59f8b03..aea0903 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -804,10 +804,27 @@ static bool do_edit(const char *filename_arg, PQExpBuffer query_buf,
}
/* \l is list databases */
- else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0)
- success = listAllDbs(false);
- else if (strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
- success = listAllDbs(true);
+ else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
+ strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0 ||
+ strcmp(cmd, "lS") == 0 || strcmp(cmd, "listS") == 0 ||
+ strcmp(cmd, "l+S") == 0 || strcmp(cmd, "list+S") == 0 ||
+ strcmp(cmd, "lS+") == 0 || strcmp(cmd, "listS+") == 0)
+ {
+ char *pattern;
+ bool show_verbose,
+ show_system;
+
+ pattern = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, true);
+
+ show_verbose = strchr(cmd, '+') ? true : false;
+ show_system = strchr(cmd, 'S') ? true : false;
+
+ success = listAllDbs(pattern, show_verbose, show_system);
+
+ if (pattern)
+ free(pattern);
+ }
/*
* large object things
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 87174cc..c6ac40c 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -637,7 +637,7 @@ static bool describeOneTSConfig(const char *oid, const char *nspname,
* for \l, \list, and -l switch
*/
bool
-listAllDbs(bool verbose)
+listAllDbs(const char *pattern, bool verbose, bool showSystem)
{
PGresult *res;
PQExpBufferData buf;
@@ -680,6 +680,14 @@ static bool describeOneTSConfig(const char *oid, const char *nspname,
if (verbose && pset.sversion >= 80000)
appendPQExpBuffer(&buf,
" JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
+
+ if (pattern)
+ processSQLNamePattern(pset.db, &buf, pattern, false, false,
+ NULL, "d.datname", NULL, NULL);
+
+ if (!showSystem && !pattern)
+ appendPQExpBuffer(&buf, "WHERE d.datname NOT IN ('postgres', 'template0', 'template1')\n");
+
appendPQExpBuffer(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data, false);
termPQExpBuffer(&buf);
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 9e71a88..721c363 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -55,7 +55,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
extern bool listTSTemplates(const char *pattern, bool verbose);
/* \l */
-extern bool listAllDbs(bool verbose);
+extern bool listAllDbs(const char *pattern, bool verbose, bool showSystem);
/* \dt, \di, \ds, \dS, etc. */
extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 1070bc5..ab32b32 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -230,7 +230,7 @@
fprintf(output, _(" \\dE[S+] [PATTERN] list foreign tables\n"));
fprintf(output, _(" \\dx[+] [PATTERN] list extensions\n"));
fprintf(output, _(" \\dy [PATTERN] list event triggers\n"));
- fprintf(output, _(" \\l[+] list all databases\n"));
+ fprintf(output, _(" \\l[S+] [PATTERN] list databases\n"));
fprintf(output, _(" \\sf[+] FUNCNAME show a function's definition\n"));
fprintf(output, _(" \\z [PATTERN] same as \\dp\n"));
fprintf(output, "\n");
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index a59f45b..1ed8e66 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -260,7 +260,7 @@ static void parse_psql_options(int argc, char *argv[],
if (!options.no_psqlrc)
process_psqlrc(argv[0]);
- success = listAllDbs(false);
+ success = listAllDbs(NULL, false, true);
PQfinish(pset.db);
exit(success ? EXIT_SUCCESS : EXIT_FAILURE);
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
