psql \l to accept patterns

Started by Peter Eisentrautabout 13 years ago16 messages

peter_e@gmx.net

about 13 years ago

1 attachment(s)

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.

Attachments:

pg-psql-l-pattern.patchtext/x-patch; charset=UTF-8; name=pg-psql-l-pattern.patchDownload

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);
 	}

Fabrízio de Royes Mello

fabriziomello@gmail.com

about 13 years ago

In reply to: Peter Eisentraut (#1)

Re: psql \l to accept patterns

On Mon, Jan 7, 2013 at 10:14 AM, Peter Eisentraut <peter_e@gmx.net> wrote:

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.

I applied the attached patch to the current master branch and everything is
ok.

When build all works fine too... and I do some tests:

1) Now '\l' list only regular databases

2) The new sub-command '\lS' list regular and systems databases

postgres=# \lS
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
         +
           |          |          |             |             |
fabrizio=CTc/fabrizio
 template1 | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
         +
           |          |          |             |             |
fabrizio=CTc/fabrizio
(3 rows)

postgres=# CREATE DATABASE fabrizio;
CREATE DATABASE
postgres=# \lS
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
privileges
-----------+----------+----------+-------------+-------------+-----------------------
 fabrizio  | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
         +
           |          |          |             |             |
fabrizio=CTc/fabrizio
 template1 | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
         +
           |          |          |             |             |
fabrizio=CTc/fabrizio
(4 rows)

-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------
------------
 fabrizio  | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
          | 5945 kB | pg_default |
 postgres  | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
          | 6041 kB | pg_default | default administrative connecti
on database
 template0 | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
         +| 5945 kB | pg_default | unmodifiable empty database
           |          |          |             |             |
fabrizio=CTc/fabrizio |         |            |
 template1 | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
         +| 5945 kB | pg_default | default template for new databa
ses
           |          |          |             |             |
fabrizio=CTc/fabrizio |         |            |
(4 rows)

postgres=# DROP DATABASE fabrizio ;
DROP DATABASE
postgres=# \lS
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
         +
           |          |          |             |             |
fabrizio=CTc/fabrizio
 template1 | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
         +
           |          |          |             |             |
fabrizio=CTc/fabrizio
(3 rows)

3) Now '\l[S+] [pattern]' works:

postgres=# \l *template*
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
privileges
------------+----------+----------+-------------+-------------+-----------------------
 mytemplate | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0  | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
         +
            |          |          |             |             |
fabrizio=CTc/fabrizio
 template1  | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
         +
            |          |          |             |             |
fabrizio=CTc/fabrizio
(3 rows)

4) By command line all works ok too...

$ ./bin/psql -c "\lS"
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
privileges
------------+----------+----------+-------------+-------------+-----------------------
 fabrizio   | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 mytemplate | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgis    | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres   | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0  | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
         +
            |          |          |             |             |
fabrizio=CTc/fabrizio
 template1  | fabrizio | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/fabrizio
         +
            |          |          |             |             |
fabrizio=CTc/fabrizio
(6 rows)

5) Docs and psql help was updated correctly.

The attached patch is ok for me and ready for commit.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Show quoted text

Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello

Alvaro Herrera

alvherre@2ndquadrant.com

about 13 years ago

In reply to: Peter Eisentraut (#1)

Re: psql \l to accept patterns

Peter Eisentraut wrote:

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.

How does this affect psql -l? Should it, for instance, hide system DBs?
Accept an optional pattern?

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas

robertmhaas@gmail.com

about 13 years ago

In reply to: Peter Eisentraut (#1)

Re: psql \l to accept patterns

On Mon, Jan 7, 2013 at 7:14 AM, Peter Eisentraut <peter_e@gmx.net> wrote:

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.

-1 from me on that last bit. I don't think that you can really
compare the postgres or template1 database to, say, the pg_toast
schema. There's no real reason for people to ever care about objects
in the pg_toast schema, but the same cannot be said about template1,
which it's often necessary to connect to when running many of the
commands (vacuumdb -a, etc.) we ship with our distribution. I think
this will just be confusing to users without any real upside.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tom Lane

tgl@sss.pgh.pa.us

about 13 years ago

In reply to: Robert Haas (#4)

Re: psql \l to accept patterns

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Jan 7, 2013 at 7:14 AM, Peter Eisentraut <peter_e@gmx.net> wrote:

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.

-1 from me on that last bit. I don't think that you can really
compare the postgres or template1 database to, say, the pg_toast
schema. There's no real reason for people to ever care about objects
in the pg_toast schema, but the same cannot be said about template1,
which it's often necessary to connect to when running many of the
commands (vacuumdb -a, etc.) we ship with our distribution. I think
this will just be confusing to users without any real upside.

Suppressing the postgres DB is even worse.

I think that it might be sensible to have an "S" option and define
"system" DBs as those without datallowconn, which ordinarily would only
hide template0. But I can't get real excited about that. People do
need to know about the existence of template0 (for use in
CREATE DATABASE ... TEMPLATE ...), which is not so true of, say,
pg_temp_NNN schemas. The "it reduces clutter" argument also seems
pretty weak if we're only hiding one database, or even three of them.

On the whole I lean towards not adding this notion.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Peter Eisentraut

peter_e@gmx.net

about 13 years ago

In reply to: Robert Haas (#4)

Re: psql \l to accept patterns

On 1/7/13 3:53 PM, Robert Haas wrote:

On Mon, Jan 7, 2013 at 7:14 AM, Peter Eisentraut <peter_e@gmx.net> wrote:

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.

-1 from me on that last bit. I don't think that you can really
compare the postgres or template1 database to, say, the pg_toast
schema. There's no real reason for people to ever care about objects
in the pg_toast schema, but the same cannot be said about template1,
which it's often necessary to connect to when running many of the
commands (vacuumdb -a, etc.) we ship with our distribution. I think
this will just be confusing to users without any real upside.

We removed showing system functions and operators from \df and \do
without S. Those are needed all the time. This was controversial at
the time, but it's the way it is now. The definition of "S", I suppose,
is more like "is there after database is created", not "typical users
care about these objects".

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas

robertmhaas@gmail.com

about 13 years ago

In reply to: Peter Eisentraut (#6)

Re: psql \l to accept patterns

On Mon, Jan 7, 2013 at 5:14 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

We removed showing system functions and operators from \df and \do
without S. Those are needed all the time. This was controversial at
the time, but it's the way it is now. The definition of "S", I suppose,
is more like "is there after database is created", not "typical users
care about these objects".

System functions and operators are needed all the time, but so are
system tables and views, and the old behavior was that the latter were
suppressed by default and the former were included by default. So I
consider that change to be well-justified on consistency grounds.
There's a practical consideration, as well. Out of the box, there are
2400 entries for functions and 3 for databases. This means that the
old \df behavior made it very hard to figure out what user-defined
functions exist in your database, but there's no corresponding problem
with \l. Finally, note that you can drop the postgres database (and
everything else will still work) but you cannot drop
pg_table_is_visible(oid), because, as the error message will inform
you, it is required by the database system.

If we make the postgres database undroppable, unrenamable, and
strictly read-only, I will happily support a proposal to consider it a
system object. Until then, it's no more a system object than the
public schema - which, you will note, \dn has no compunctions about
displaying, even without S.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Peter Eisentraut

peter_e@gmx.net

about 13 years ago

In reply to: Robert Haas (#7)

Re: psql \l to accept patterns

On Mon, 2013-01-07 at 17:37 -0500, Robert Haas wrote:

If we make the postgres database undroppable, unrenamable, and
strictly read-only, I will happily support a proposal to consider it a
system object. Until then, it's no more a system object than the
public schema - which, you will note, \dn has no compunctions about
displaying, even without S.

Good point. What about the other suggestion about only displaying
databases by default that you can connect to?

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas

robertmhaas@gmail.com

about 13 years ago

In reply to: Peter Eisentraut (#8)

Re: psql \l to accept patterns

On Tue, Jan 8, 2013 at 11:36 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On Mon, 2013-01-07 at 17:37 -0500, Robert Haas wrote:

If we make the postgres database undroppable, unrenamable, and
strictly read-only, I will happily support a proposal to consider it a
system object. Until then, it's no more a system object than the
public schema - which, you will note, \dn has no compunctions about
displaying, even without S.

Good point. What about the other suggestion about only displaying
databases by default that you can connect to?

I would tend not to adopt that suggestion, on the grounds that it has
no obvious parallel with anything else psql hides by default.
However, I don't feel quite as strongly about that case.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#10

Stephen Frost

sfrost@snowman.net

about 13 years ago

In reply to: Robert Haas (#9)

Re: psql \l to accept patterns

* Robert Haas (robertmhaas@gmail.com) wrote:

On Tue, Jan 8, 2013 at 11:36 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

Good point. What about the other suggestion about only displaying
databases by default that you can connect to?

I would tend not to adopt that suggestion, on the grounds that it has
no obvious parallel with anything else psql hides by default.
However, I don't feel quite as strongly about that case.

In the past, haven't we done this through the catalog tables themselves
rather than hacking up psql..? pg_stats being a prime example? With
the row-level-security discussion, there was talk about if we might be
able to apply that capability to catalogs also. That strikes me as a
better option/approach than doing any of this in one particular
application (psql in this case) which connects to PG.

tbh, I'm not entirely against excluding databases that don't allow *any*
connections (key'd off datallowconns) to clear out template0/template1
from the default list, but I see that as different from "things I don't
have permissions to".

Thanks,

Stephen

#11

Peter Eisentraut

peter_e@gmx.net

almost 13 years ago

In reply to: Peter Eisentraut (#1)

1 attachment(s)

Re: psql \l to accept patterns

On Mon, 2013-01-07 at 07:14 -0500, Peter Eisentraut wrote:

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.

Most people didn't like the "S" option, so here is a revised patch that
just adds the pattern support.

Attachments:

pg-psql-l-pattern.patchtext/x-patch; charset=UTF-8; name=pg-psql-l-pattern.patchDownload

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 4c87d8a..8d0095e 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1689,12 +1689,13 @@ <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[+]</literal> or <literal>\list[+]</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 their 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
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 20c45e2..e40f8b2 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -804,10 +804,22 @@ 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)
+	{
+		char	   *pattern;
+		bool		show_verbose;
+
+		pattern = psql_scan_slash_option(scan_state,
+										 OT_NORMAL, NULL, true);
+
+		show_verbose = strchr(cmd, '+') ? true : false;
+
+		success = listAllDbs(pattern, show_verbose);
+
+		if (pattern)
+			free(pattern);
+	}
 
 	/*
 	 * large object things
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 8064a3d..046513d 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -641,7 +641,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)
 {
 	PGresult   *res;
 	PQExpBufferData buf;
@@ -684,6 +684,11 @@ 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);
+
 	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..09b6237 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);
 
 /* \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 fd7effa..2cbdd83 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -233,7 +233,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[+]   [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..5cb6b5f 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);
 		PQfinish(pset.db);
 		exit(success ? EXIT_SUCCESS : EXIT_FAILURE);
 	}

#12

Satoshi Nagayasu

snaga@uptime.jp

almost 13 years ago

In reply to: Peter Eisentraut (#11)

Re: psql \l to accept patterns

Hi,

I have tried this patch.

https://commitfest.postgresql.org/action/patch_view?id=1051

2013/01/29 14:48, Peter Eisentraut wrote:

On Mon, 2013-01-07 at 07:14 -0500, Peter Eisentraut wrote:

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.

Most people didn't like the "S" option, so here is a revised patch that
just adds the pattern support.

It seems working well with the latest git master.
I think it's good enough to be committed.

BTW, is there any good place to put new regression test for the psql
command? I couldn't find it out.

Any comment or suggestion?

Regards,
--
Satoshi Nagayasu <snaga@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#13

Tom Lane

tgl@sss.pgh.pa.us

almost 13 years ago

In reply to: Satoshi Nagayasu (#12)

Re: psql \l to accept patterns

Satoshi Nagayasu <snaga@uptime.jp> writes:

On Mon, 2013-01-07 at 07:14 -0500, Peter Eisentraut wrote:

Here is a patch for psql's \l command to accept patterns, like \d

BTW, is there any good place to put new regression test for the psql
command? I couldn't find it out.

As far as a test for this specific feature goes, I'd be against adding
one, because it'd be likely to result in random failures in "make
installcheck" mode, depending on what other databases are in the
installation.

More generally, we've tended to put tests of \d-style psql features
together with the relevant backend-side tests.

The proposed patch to add \gset adds a separate regression test file
specifically for psql. I've been debating whether that was worth
committing; but if there's near-term interest in adding any more tests
for psql features that aren't closely connected to backend features,
maybe it's worth having such a file.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#14

Satoshi Nagayasu

snaga@uptime.jp

almost 13 years ago

In reply to: Tom Lane (#13)

Re: psql \l to accept patterns

(2013/01/30 0:34), Tom Lane wrote:

Satoshi Nagayasu <snaga@uptime.jp> writes:

On Mon, 2013-01-07 at 07:14 -0500, Peter Eisentraut wrote:

Here is a patch for psql's \l command to accept patterns, like \d

BTW, is there any good place to put new regression test for the psql
command? I couldn't find it out.

As far as a test for this specific feature goes, I'd be against adding
one, because it'd be likely to result in random failures in "make
installcheck" mode, depending on what other databases are in the
installation.

More generally, we've tended to put tests of \d-style psql features
together with the relevant backend-side tests.

Yes, I think so too.

First of all, I was looking for some regression tests for
CREATE/ALTER/DROP DATABASE commands, but I couldn't find them
in the test/regress/sql/ directory. So, I asked the question.

I guess these database tests are in pg_regress.c. Right?

The proposed patch to add \gset adds a separate regression test file
specifically for psql. I've been debating whether that was worth
committing; but if there's near-term interest in adding any more tests
for psql features that aren't closely connected to backend features,
maybe it's worth having such a file.

Personally, I'm interested in having regression tests whatever
the target is, because software tends to be more complicated.
So, if we reach consensus to have dedicated tests for the psql
command (or other client-side commands), I wish to contribute to it.

Regards,
--
Satoshi Nagayasu <snaga@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#15

Tom Lane

tgl@sss.pgh.pa.us

almost 13 years ago

In reply to: Satoshi Nagayasu (#14)

Re: psql \l to accept patterns

Satoshi Nagayasu <snaga@uptime.jp> writes:

First of all, I was looking for some regression tests for
CREATE/ALTER/DROP DATABASE commands, but I couldn't find them
in the test/regress/sql/ directory. So, I asked the question.

I guess these database tests are in pg_regress.c. Right?

Yeah, we don't bother with explicit tests of CREATE/DROP DATABASE
because that's inherently tested by creating/replacing the regression
database(s). And these actions are expensive enough that I'm not
eager to add several more of them to the test sequence without darn
good reason. I'm not sure how much of ALTER DATABASE's functionality
we're testing, though as you say pg_regress itself does some of that.
It might be reasonable to add some more tests of ALTER cases.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#16

Peter Eisentraut

peter_e@gmx.net

almost 13 years ago

In reply to: Satoshi Nagayasu (#12)

Re: psql \l to accept patterns

On Wed, 2013-01-30 at 00:20 +0900, Satoshi Nagayasu wrote:

It seems working well with the latest git master.
I think it's good enough to be committed.

Committed, thanks.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers