Merge pull request #886 from catherinedevlin/ev_command

\ev and \ef commands

4 files changed, 112 insertions, 9 deletions

diff --git a/changelog.rst b/changelog.rst
index d07faa01..266b6a69 100644
--- a/changelog.rst
+++ b/changelog.rst
@@ -11,6 +11,7 @@ Internal changes:
 
 * Mark tests requiring a running database server as dbtest (Thanks: `Dick Marinus`_)
 * Add ``application_name`` to help identify pgcli connection to database (issue #868) (Thanks: `François Pietka`_)
+* Support `\\ev``, ``\ef`` (issue #) (Thanks: `Catherine Devlin`_)
 * Add an is_special command flag to MetaQuery (Thanks: `Rishi Ramraj`_)
 * Ported Destructive Warning from mycli.
 * Refactor Destructive Warning behave tests (Thanks: `Dick Marinus`_)
diff --git a/pgcli/main.py b/pgcli/main.py
index f577a1c8..f2903b7f 100644
--- a/pgcli/main.py
+++ b/pgcli/main.py
@@ -480,19 +480,29 @@ class PGCli(object):
         # We may find a better way to do it in the future.
         saved_callables = cli.application.pre_run_callables
         try:
-            while special.editor_command(document.text):
-                filename = special.get_filename(document.text)
-                query = (special.get_editor_query(document.text) or
-                         self.get_last_query())
+            editor_command = special.editor_command(document.text)
+            while editor_command:
+                if editor_command == '\\e':
+                    filename = special.get_filename(document.text)
+                    query = (special.get_editor_query(document.text) or
+                             self.get_last_query())
+                else:  # \ev or \ef
+                    filename = None
+                    spec = document.text.split()[1]
+                    if editor_command == '\\ev':
+                        query = self.pgexecute.view_definition(spec)
+                    elif editor_command == '\\ef':
+                        query = self.pgexecute.function_definition(spec)
                 sql, message = special.open_external_editor(
                     filename, sql=query)
                 if message:
                     # Something went wrong. Raise an exception and bail.
                     raise RuntimeError(message)
-                cli.current_buffer.document = Document(sql,
-                                                       cursor_position=len(sql))
+                cli.current_buffer.document = Document(
+                    sql, cursor_position=len(sql))
                 cli.application.pre_run_callables = []
                 document = cli.run()
+                editor_command = special.editor_command(document.text)
         finally:
             cli.application.pre_run_callables = saved_callables
         return document
@@ -599,14 +609,14 @@ class PGCli(object):
                     continue
 
                 self.watch_command, timing = special.get_watch_command(
-                        document.text)
+                    document.text)
                 if self.watch_command:
                     while self.watch_command:
                         try:
                             query = self.execute_command(self.watch_command)
                             click.echo(
-                                    'Waiting for {0} seconds before repeating'
-                                    .format(timing))
+                                'Waiting for {0} seconds before repeating'
+                                .format(timing))
                             sleep(timing)
                         except KeyboardInterrupt:
                             self.watch_command = None
diff --git a/pgcli/pgexecute.py b/pgcli/pgexecute.py
index 149efc51..29dfa1ba 100644
--- a/pgcli/pgexecute.py
+++ b/pgcli/pgexecute.py
@@ -162,6 +162,27 @@ class PGExecute(object):
         WHERE name = 'unix_socket_directories'
     '''
 
+    view_definition_query = '''
+        WITH v AS (SELECT %s::pg_catalog.regclass::pg_catalog.oid AS v_oid)
+        SELECT nspname, relname, relkind, 
+               pg_catalog.pg_get_viewdef(c.oid, true), 
+               array_remove(array_remove(c.reloptions,'check_option=local'),
+                            'check_option=cascaded') AS reloptions, 
+               CASE 
+                 WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text 
+                 WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text 
+                 ELSE NULL 
+               END AS checkoption 
+        FROM pg_catalog.pg_class c 
+        LEFT JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid)
+        JOIN v ON (c.oid = v.v_oid)'''
+
+    function_definition_query = '''
+        WITH f AS 
+            (SELECT %s::pg_catalog.regproc::pg_catalog.oid AS f_oid)
+        SELECT pg_catalog.pg_get_functiondef(f.f_oid)
+        FROM f'''
+
     def __init__(self, database, user, password, host, port, dsn, **kwargs):
         self.dbname = database
         self.user = user
@@ -384,6 +405,38 @@ class PGExecute(object):
                 cur.execute(fallback)
                 return cur.fetchone()[0]
 
+    def view_definition(self, spec):
+        """Returns the SQL defining views described by `spec`"""
+
+        template = 'CREATE OR REPLACE {6} VIEW {0}.{1} AS \n{3}'
+        # 2: relkind, v or m (materialized)
+        # 4: reloptions, null
+        # 5: checkoption: local or cascaded
+        with self.conn.cursor() as cur:
+            sql = self.view_definition_query
+            _logger.debug('View Definition Query. sql: %r\nspec: %r',
+                          sql, spec)
+            try:
+                cur.execute(sql, (spec, ))
+            except psycopg2.ProgrammingError:
+                raise RuntimeError('View {} does not exist.'.format(spec))
+            result = cur.fetchone()
+            view_type = 'MATERIALIZED' if result[2] == 'm' else ''
+            return template.format(*result + (view_type,))
+
+    def function_definition(self, spec):
+        """Returns the SQL defining functions described by `spec`"""
+
+        with self.conn.cursor() as cur:
+            sql = self.function_definition_query
+            _logger.debug('Function Definition Query. sql: %r\nspec: %r',
+                          sql, spec)
+            try:
+                cur.execute(sql, (spec,))
+                result = cur.fetchone()
+                return result[0]
+            except psycopg2.ProgrammingError:
+                raise RuntimeError('Function {} does not exist.'.format(spec))
 
     def schemata(self):
         """Returns a list of schema names in the database"""
diff --git a/tests/test_pgexecute.py b/tests/test_pgexecute.py
index 56067906..da055fb6 100644
--- a/tests/test_pgexecute.py
+++ b/tests/test_pgexecute.py
@@ -360,3 +360,42 @@ def test_on_error_stop(executor, exception_formatter):
 #     sql = "DO language plpgsql $$ BEGIN RAISE NOTICE '有人更改'; END $$;"
 #     result = list(executor.run(sql))
 #     assert result[0][0] == u'NOTICE:  有人更改\n'
+
+
+@dbtest
+def test_nonexistent_function_definition(executor):
+    with pytest.raises(RuntimeError):
+        result = executor.view_definition('there_is_no_such_function')
+
+
+@dbtest
+def test_function_definition(executor):
+    run(executor, '''
+            CREATE OR REPLACE FUNCTION public.the_number_three()
+            RETURNS int
+            LANGUAGE sql
+            AS $function$
+              select 3;
+            $function$
+    ''')
+    result = executor.function_definition('the_number_three')
+
+
+@dbtest
+def test_view_definition(executor):
+    run(executor, 'create table tbl1 (a text, b numeric)')
+    run(executor, 'create view vw1 AS SELECT * FROM tbl1')
+    run(executor, 'create materialized view mvw1 AS SELECT * FROM tbl1')
+    result = executor.view_definition('vw1')
+    assert 'FROM tbl1' in result
+    # import pytest; pytest.set_trace()
+    result = executor.view_definition('mvw1')
+    assert 'MATERIALIZED VIEW' in result
+
+
+@dbtest
+def test_nonexistent_view_definition(executor):
+    with pytest.raises(RuntimeError):
+        result = executor.view_definition('there_is_no_such_view')
+    with pytest.raises(RuntimeError):
+        result = executor.view_definition('mvw1')