Merge pull request #746 from fpietka/list-databases

List databases

3 files changed, 36 insertions, 2 deletions

diff --git a/changelog.rst b/changelog.rst
index 59a11540..96e4c01e 100644
--- a/changelog.rst
+++ b/changelog.rst
@@ -8,6 +8,7 @@ Upcoming
 * Quote schema in `SET SCHEMA` statement (issue #469) (Thanks: `Irina Truong`_)
 * Use CLI Helpers for pretty printing query results (Thanks: `Thomas Roten`_).
 * Skip serial columns when expanding * for `INSERT INTO foo(*` (Thanks: `Joakim Koljonen`_).
+* Command line option to list databases (issue #206) (Thanks: `François Pietka`_)
 
 1.6.0
 =====
diff --git a/pgcli/main.py b/pgcli/main.py
index 70bd749e..3d826f81 100755
--- a/pgcli/main.py
+++ b/pgcli/main.py
@@ -787,11 +787,13 @@ class PGCli(object):
         default=False,
         help='Skip intro on startup and goodbye on exit.')
 @click.option('--prompt', help='Prompt format (Default: "\\u@\\h:\\d> ").')
+@click.option('-l', '--list', 'list_databases', is_flag=True, help='list '
+              'available databases, then exit.')
 @click.argument('database', default=lambda: None, envvar='PGDATABASE', nargs=1)
 @click.argument('username', default=lambda: None, envvar='PGUSER', nargs=1)
 def cli(database, username_opt, host, port, prompt_passwd, never_prompt,
-    single_connection, dbname, username, version, pgclirc, dsn, row_limit,
-    less_chatty, prompt):
+        single_connection, dbname, username, version, pgclirc, dsn, row_limit,
+        less_chatty, prompt, list_databases):
 
     if version:
         print('Version:', __version__)
@@ -840,6 +842,19 @@ def cli(database, username_opt, host, port, prompt_passwd, never_prompt,
     else:
         pgcli.connect(database, host, user, port)
 
+    if list_databases:
+        cur, headers, status = pgcli.pgexecute.full_databases()
+
+        title = 'List of databases'
+        settings = OutputSettings(
+            table_format='ascii',
+            missingval='<null>'
+        )
+        formatted = format_output(title, cur, headers, status, settings)
+        click.echo_via_pager('\n'.join(formatted))
+
+        sys.exit(0)
+
     pgcli.logger.debug('Launch Params: \n'
             '\tdatabase: %r'
             '\tuser: %r'
diff --git a/pgcli/pgexecute.py b/pgcli/pgexecute.py
index a394f05b..a3ebb8b3 100644
--- a/pgcli/pgexecute.py
+++ b/pgcli/pgexecute.py
@@ -146,6 +146,16 @@ class PGExecute(object):
         FROM pg_catalog.pg_database d
         ORDER BY 1'''
 
+    full_databases_query = '''
+        SELECT d.datname as "Name",
+            pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
+            pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
+            d.datcollate as "Collate",
+            d.datctype as "Ctype",
+            pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
+        FROM pg_catalog.pg_database d
+        ORDER BY 1'''
+
     def __init__(self, database, user, password, host, port, dsn, **kwargs):
         self.dbname = database
         self.user = user
@@ -471,6 +481,14 @@ class PGExecute(object):
             cur.execute(self.databases_query)
             return [x[0] for x in cur.fetchall()]
 
+    def full_databases(self):
+        with self.conn.cursor() as cur:
+            _logger.debug('Databases Query. sql: %r',
+                          self.full_databases_query)
+            cur.execute(self.full_databases_query)
+            headers = [x[0] for x in cur.description]
+            return cur.fetchall(), headers, cur.statusmessage
+
     def foreignkeys(self):
         """Yields ForeignKey named tuples"""