Merge pull request #144 from darikg/special_list

make \dt and \dv use verbose and pattern arguments

1 files changed, 71 insertions, 34 deletions

diff --git a/pgcli/packages/pgspecial.py b/pgcli/packages/pgspecial.py
index 0de26029..48efb82a 100644
--- a/pgcli/packages/pgspecial.py
+++ b/pgcli/packages/pgspecial.py
@@ -50,6 +50,74 @@ def list_schemas(cur, pattern, verbose):
         headers = [x[0] for x in cur.description]
         return [(cur, headers, cur.statusmessage)]
 
+def list_objects(cur, pattern, verbose, relkinds):
+    """
+        Returns (rows, header, status)
+
+        This method is used by list_tables, list_views, and list_indexes
+
+        relkinds is a list of strings to filter pg_class.relkind
+
+    """
+    schema_pattern, table_pattern = sql_name_pattern(pattern)
+
+    if verbose:
+        verbose_columns = '''
+            ,pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
+            pg_catalog.obj_description(c.oid, 'pg_class') as "Description" '''
+    else:
+        verbose_columns = ''
+
+    sql = '''SELECT n.nspname as "Schema",
+                    c.relname as "Name",
+                    CASE c.relkind
+                      WHEN 'r' THEN 'table' WHEN 'v' THEN 'view'
+                      WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index'
+                      WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special'
+                      WHEN 'f' THEN 'foreign table' END
+                    as "Type",
+                    pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
+          ''' + verbose_columns + '''
+            FROM    pg_catalog.pg_class c
+                    LEFT JOIN pg_catalog.pg_namespace n
+                      ON n.oid = c.relnamespace
+            WHERE   c.relkind = ANY(%s)
+                    AND n.nspname <> 'pg_catalog'
+                    AND n.nspname <> 'information_schema'
+                    AND n.nspname !~ '^pg_toast' '''
+
+    params = [relkinds]
+
+    if schema_pattern:
+        sql += ' AND n.nspname ~ %s'
+        params.append(schema_pattern)
+
+    if table_pattern:
+        sql += ' AND c.relname ~ %s'
+        params.append(table_pattern)
+
+    sql = cur.mogrify(sql + ' ORDER BY 1, 2', params)
+
+    log.debug(sql)
+    cur.execute(sql)
+
+    if cur.description:
+        headers = [x[0] for x in cur.description]
+        return [(cur, headers, cur.statusmessage)]
+
+
+def list_tables(cur, pattern, verbose):
+    return list_objects(cur, pattern, verbose, ['r', ''])
+
+
+def list_views(cur, pattern, verbose):
+    return list_objects(cur, pattern, verbose, ['v', 's', ''])
+
+
+def list_indexes(cur, pattern, verbose):
+    return list_objects(cur, pattern, verbose, ['i', 's', ''])
+
+
 def describe_table_details(cur, pattern, verbose):
     """
     Returns (rows, headers, status)
@@ -787,40 +855,9 @@ CASE_SENSITIVE_COMMANDS = {
             '\dn': (list_schemas, ['\dn[+] [pattern]', 'list schemas']),
             '\\x': (expanded_output, ['\\x', 'Toggle expanded output.']),
             '\\timing': (toggle_timing, ['\\timing', 'Toggle timing of commands.']),
-            '\dt': ('''SELECT n.nspname as "Schema", c.relname as "Name", CASE
-            c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN
-            'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
-            WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as
-            "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM
-            pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid
-            = c.relnamespace WHERE c.relkind IN ('r','') AND n.nspname <>
-            'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~
-            '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY
-            1,2;''', ['\dt', 'list tables.']),
-            '\di': ('''SELECT n.nspname as "Schema", c.relname as "Name", CASE
-            c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN
-            'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
-            WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as
-            "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
-            c2.relname as "Table" FROM pg_catalog.pg_class c LEFT JOIN
-            pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN
-            pg_catalog.pg_index i ON i.indexrelid = c.oid LEFT JOIN
-            pg_catalog.pg_class c2 ON i.indrelid = c2.oid WHERE c.relkind
-            IN ('i','') AND n.nspname <> 'pg_catalog' AND
-            n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'
-            AND pg_catalog.pg_table_is_visible(c.oid)
-            ORDER BY 1,2;''', ['\di', 'list indexes.']),
-            '\dv': ('''SELECT n.nspname as "Schema", c.relname as "Name", CASE
-            c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN
-            'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
-            WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as
-            "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM
-            pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON
-            n.oid = c.relnamespace WHERE c.relkind IN ('v','') AND
-            n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'
-            AND n.nspname !~ '^pg_toast' AND
-            pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2;''',
-            ['\dv', 'list views.']),
+            '\\dt': (list_tables, ['\\dt[+] [pattern]', 'list tables.']),
+            '\\di': (list_indexes, ['\\di[+] [pattern]', 'list indexes.']),
+            '\\dv': (list_views, ['\\dv[+] [pattern]', 'list views.']),
             }
 
 NON_CASE_SENSITIVE_COMMANDS = {