Hackers!
I'm proposing to add two new subcommands in psql:
1. \ev viewname - edit view definition with external editor (like \ef for
function)
2. \sv viewname - show view definition (like \sf for function, for
consistency)
What's inside:
1. review-ready implementation of \ev and \sv psql subcommands for editing
and viewing view's definition.
2. psql's doc update with new subcommands description.
3. a bit of extracting common source code parts into separate functions.
4. psql internal help update.
5. tab completion update.
There is one narrow place in this patch: current implementation doesn't
support "create" statement formatting for recursive views.
Any comments? Suggestions?
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 62a3b21..d668777 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1651,6 +1651,28 @@ Tue Oct 26 21:40:57 CEST 1999
<varlistentry>
+ <term><literal>\ev <optional> <replaceable
class="parameter">viewname</> </optional> </literal></term>
+
+ <listitem>
+ <para>
+ This command fetches and edits the definition of the named view,
+ in the form of a <command>CREATE OR REPLACE VIEW</> command.
+ Editing is done in the same way as for <literal>\edit</>.
+ After the editor exits, the updated command waits in the query buffer;
+ type semicolon or <literal>\g</> to send it, or <literal>\r</>
+ to cancel.
+ </para>
+
+ <para>
+ If no view is specified, a blank <command>CREATE VEIW</>
+ template is presented for editing.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
<term><literal>\encoding [ <replaceable
class="parameter">encoding</replaceable> ]</literal></term>
<listitem>
@@ -2522,6 +2544,25 @@ testdb=> <userinput>\setenv LESS -imx4F</userinput>
<varlistentry>
+ <term><literal>\sv[+] <replaceable class="parameter">viewname</>
</literal></term>
+
+ <listitem>
+ <para>
+ This command fetches and shows the definition of the named view,
+ in the form of a <command>CREATE OR REPLACE VIEW</> command.
+ The definition is printed to the current query output channel,
+ as set by <command>\o</command>.
+ </para>
+
+ <para>
+ If <literal>+</literal> is appended to the command name, then the
+ output lines are numbered, with the first line of the view definition
+ being line 1.
+ </para>
+ </varlistentry>
+
+
+ <varlistentry>
<term><literal>\t</literal></term>
<listitem>
<para>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 70b7d3b..948e381 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -60,9 +60,17 @@ static bool do_connect(char *dbname, char *user, char *host,
char *port);
static bool do_shell(const char *command);
static bool do_watch(PQExpBuffer query_buf, long sleep);
static bool lookup_function_oid(const char *desc, Oid *foid);
+static bool lookup_view_oid(const char *desc, Oid *view_oid);
static bool get_create_function_cmd(Oid oid, PQExpBuffer buf);
-static int strip_lineno_from_funcdesc(char *func);
+static bool get_create_view_cmd(Oid oid, PQExpBuffer buf);
+static void format_create_view_cmd(char *view, PQExpBuffer buf);
+static int strip_lineno_from_objdesc(char *func);
static void minimal_error_message(PGresult *res);
+static int count_lines_in_buf(PQExpBuffer buf);
+static void print_with_linenumbers(FILE *output,
+ char *lines,
+ const char
*header_cmp_keyword,
+ size_t
header_cmp_sz);
static void printSSLInfo(void);
static bool printPsetInfo(const char *param, struct printQueryOpt *popt);
@@ -612,7 +620,7 @@ exec_command(const char *cmd,
func = psql_scan_slash_option(scan_state,
OT_WHOLE_LINE, NULL, true);
- lineno = strip_lineno_from_funcdesc(func);
+ lineno = strip_lineno_from_objdesc(func);
if (lineno == 0)
{
/* error already reported */
@@ -682,6 +690,77 @@ exec_command(const char *cmd,
}
}
+ /*
+ * \ev -- edit the named view, or present a blank CREATE VIEW viewname
AS
+ * template if no argument is given
+ */
+ else if (strcmp(cmd, "ev") == 0)
+ {
+ int lineno = -1;
+
+ if (pset.sversion < 70100)
+ {
+ psql_error("The server (version %d.%d) does not support
editing view definition.\n",
+ pset.sversion / 10000,
(pset.sversion / 100) % 100);
+ status = PSQL_CMD_ERROR;
+ }
+ else if (!query_buf)
+ {
+ psql_error("no query buffer\n");
+ status = PSQL_CMD_ERROR;
+ }
+ else
+ {
+ char *view;
+ Oid view_oid = InvalidOid;
+
+ view = psql_scan_slash_option(scan_state,
+
OT_WHOLE_LINE, NULL, true);
+ lineno = strip_lineno_from_objdesc(view);
+
+ if (lineno == 0)
+ {
+ /* error already reported */
+ status = PSQL_CMD_ERROR;
+ }
+ if (!view)
+ {
+ /* set up an empty command to fill in */
+ printfPQExpBuffer(query_buf,
+ "CREATE VIEW
viewname AS \n"
+ " SELECT \n"
+ " --
something... \n");
+ }
+ else if (!lookup_view_oid(view, &view_oid))
+ {
+ /* error already reported */
+ status = PSQL_CMD_ERROR;
+ }
+ else if (!get_create_view_cmd(view_oid, query_buf))
+ {
+ /* error already reported */
+ status = PSQL_CMD_ERROR;
+ }
+
+ if (view) {
+ format_create_view_cmd(view, query_buf);
+ free(view);
+ }
+ }
+
+ if (status != PSQL_CMD_ERROR)
+ {
+ bool edited = false;
+
+ if (!do_edit(NULL, query_buf, lineno, &edited))
+ status = PSQL_CMD_ERROR;
+ else if (!edited)
+ puts(_("No changes"));
+ else
+ status = PSQL_CMD_NEWEDIT;
+ }
+ }
+
/* \echo and \qecho */
else if (strcmp(cmd, "echo") == 0 || strcmp(cmd, "qecho") == 0)
{
@@ -1253,18 +1332,7 @@ exec_command(const char *cmd,
if (pset.queryFout == stdout)
{
/* count lines in function to see if pager is
needed */
- int lineno = 0;
- const char *lines = func_buf->data;
-
- while (*lines != '\0')
- {
- lineno++;
- /* find start of next line */
- lines = strchr(lines, '\n');
- if (!lines)
- break;
- lines++;
- }
+ int lineno = count_lines_in_buf(func_buf);
output = PageOutput(lineno, &(pset.popt.topt));
is_pager = true;
@@ -1278,10 +1346,6 @@ exec_command(const char *cmd,
if (show_linenumbers)
{
- bool in_header = true;
- int lineno = 0;
- char *lines = func_buf->data;
-
/*
* lineno "1" should correspond to the first
line of the
* function body. We expect that
pg_get_functiondef() will
@@ -1291,32 +1355,9 @@ exec_command(const char *cmd,
*
* Note that this loop scribbles on func_buf.
*/
- while (*lines != '\0')
- {
- char *eol;
-
- if (in_header && strncmp(lines, "AS ",
3) == 0)
- in_header = false;
- /* increment lineno only for body's
lines */
- if (!in_header)
- lineno++;
-
- /* find and mark end of current line */
- eol = strchr(lines, '\n');
- if (eol != NULL)
- *eol = '\0';
-
- /* show current line as appropriate */
- if (in_header)
- fprintf(output, " %s\n",
lines);
- else
- fprintf(output, "%-7d %s\n",
lineno, lines);
-
- /* advance to next line, if any */
- if (eol == NULL)
- break;
- lines = ++eol;
- }
+
+ char *lines = func_buf->data;
+ print_with_linenumbers(output, lines, "AS ", 3);
}
else
{
@@ -1333,6 +1374,81 @@ exec_command(const char *cmd,
destroyPQExpBuffer(func_buf);
}
+ /* \sv -- show a view's source code */
+ else if (strcmp(cmd, "sv") == 0 || strcmp(cmd, "sv+") == 0)
+ {
+ bool show_linenumbers = (strcmp(cmd, "sv+") == 0);
+ PQExpBuffer view_buf;
+ char *view;
+ Oid view_oid = InvalidOid;
+
+ view_buf = createPQExpBuffer();
+ view = psql_scan_slash_option(scan_state,
+
OT_WHOLE_LINE, NULL, true);
+
+ if (pset.sversion < 70100)
+ {
+ psql_error("The server (version %d.%d) does not support
showing view definition.\n",
+ pset.sversion / 10000,
(pset.sversion / 100) % 100);
+ status = PSQL_CMD_ERROR;
+ }
+ if (!view)
+ {
+ psql_error("view name is required\n");
+ status = PSQL_CMD_ERROR;
+ }
+ else if (!lookup_view_oid(view, &view_oid))
+ {
+ /* error already reported */
+ status = PSQL_CMD_ERROR;
+ }
+ else if (!get_create_view_cmd(view_oid, view_buf))
+ {
+ /* error already reported */
+ status = PSQL_CMD_ERROR;
+ }
+ else
+ {
+ FILE *output;
+ bool is_pager;
+
+ format_create_view_cmd(view, view_buf);
+
+ if (pset.queryFout == stdout)
+ {
+ /* count lines in view to see if pager is
needed */
+ int lineno = count_lines_in_buf(view_buf);
+
+ output = PageOutput(lineno, &(pset.popt.topt));
+ is_pager = true;
+ }
+ else
+ {
+ /* use previously set output file, without
pager */
+ output = pset.queryFout;
+ is_pager = false;
+ }
+
+ if (show_linenumbers)
+ {
+ char *lines = view_buf->data;
+ print_with_linenumbers(output, lines, "
SELECT", 7);
+ }
+ else
+ {
+ /* just send the function definition to output
*/
+ fputs(view_buf->data, output);
+ }
+
+ if (is_pager)
+ ClosePager(output);
+ }
+
+ if (view)
+ free(view);
+ destroyPQExpBuffer(view_buf);
+ }
+
/* \t -- turn off headers and row count */
else if (strcmp(cmd, "t") == 0)
{
@@ -3001,7 +3117,7 @@ do_watch(PQExpBuffer query_buf, long sleep)
* returns true unless we have ECHO_HIDDEN_NOEXEC.
*/
static bool
-lookup_function_echo_hidden(char * query)
+lookup_object_echo_hidden(char * query)
{
if (pset.echo_hidden != PSQL_ECHO_HIDDEN_OFF)
{
@@ -3024,35 +3140,21 @@ lookup_function_echo_hidden(char * query)
return true;
}
-/*
- * This function takes a function description, e.g. "x" or "x(int)", and
- * issues a query on the given connection to retrieve the function's OID
- * using a cast to regproc or regprocedure (as appropriate). The result,
- * if there is one, is returned at *foid. Note that we'll fail if the
- * function doesn't exist OR if there are multiple matching candidates
- * OR if there's something syntactically wrong with the function description;
- * unfortunately it can be hard to tell the difference.
- */
static bool
-lookup_function_oid(const char *desc, Oid *foid)
+lookup_object_oid_internal(PQExpBuffer query, Oid *obj_oid)
{
bool result = true;
- PQExpBuffer query;
PGresult *res;
- query = createPQExpBuffer();
- appendPQExpBufferStr(query, "SELECT ");
- appendStringLiteralConn(query, desc, pset.db);
- appendPQExpBuffer(query, "::pg_catalog.%s::pg_catalog.oid",
- strchr(desc, '(') ? "regprocedure" :
"regproc");
- if (!lookup_function_echo_hidden(query->data))
+ if (!lookup_object_echo_hidden(query->data))
{
destroyPQExpBuffer(query);
return false;
}
+
res = PQexec(pset.db, query->data);
if (PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) == 1)
- *foid = atooid(PQgetvalue(res, 0, 0));
+ *obj_oid = atooid(PQgetvalue(res, 0, 0));
else
{
minimal_error_message(res);
@@ -3066,24 +3168,58 @@ lookup_function_oid(const char *desc, Oid *foid)
}
/*
- * Fetches the "CREATE OR REPLACE FUNCTION ..." command that describes the
- * function with the given OID. If successful, the result is stored in buf.
+ * This function takes a function description, e.g. "x" or "x(int)", and
+ * issues a query on the given connection to retrieve the function's OID
+ * using a cast to regproc or regprocedure (as appropriate). The result,
+ * if there is one, is returned at *foid. Note that we'll fail if the
+ * function doesn't exist OR if there are multiple matching candidates
+ * OR if there's something syntactically wrong with the function description;
+ * unfortunately it can be hard to tell the difference.
*/
static bool
-get_create_function_cmd(Oid oid, PQExpBuffer buf)
+lookup_function_oid(const char *desc, Oid *foid)
{
- bool result = true;
PQExpBuffer query;
- PGresult *res;
query = createPQExpBuffer();
- printfPQExpBuffer(query, "SELECT pg_catalog.pg_get_functiondef(%u)",
oid);
+ appendPQExpBufferStr(query, "SELECT ");
+ appendStringLiteralConn(query, desc, pset.db);
+ appendPQExpBuffer(query, "::pg_catalog.%s::pg_catalog.oid",
+ strchr(desc, '(') ? "regprocedure" :
"regproc");
+
+ return lookup_object_oid_internal(query, foid);
+}
+
+static bool
+lookup_view_oid(const char *desc, Oid *view_oid)
+{
+ PQExpBuffer query;
+
+ query = createPQExpBuffer();
+ appendPQExpBufferStr(query, "SELECT ");
+ appendStringLiteralConn(query, desc, pset.db);
+ appendPQExpBuffer(query, "::pg_catalog.regclass::pg_catalog.oid");
+
+ return lookup_object_oid_internal(query, view_oid);
+}
+
+/*
+ * Fetches the "CREATE ..." command that describes the
+ * database object by given query.
+ * If successful, the result is stored in buf.
+ */
+static bool
+get_create_object_cmd_internal(PQExpBuffer query, PQExpBuffer buf)
+{
+ bool result = true;
+ PGresult *res;
- if (!lookup_function_echo_hidden(query->data))
+ if (!lookup_object_echo_hidden(query->data))
{
destroyPQExpBuffer(query);
return false;
}
+
res = PQexec(pset.db, query->data);
if (PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) == 1)
{
@@ -3103,6 +3239,57 @@ get_create_function_cmd(Oid oid, PQExpBuffer buf)
}
/*
+ * Fetches the "CREATE OR REPLACE FUNCTION ..." command that describes the
+ * function with the given OID. If successful, the result is stored in buf.
+ */
+static bool
+get_create_function_cmd(Oid oid, PQExpBuffer buf)
+{
+ PQExpBuffer query;
+
+ query = createPQExpBuffer();
+ printfPQExpBuffer(query, "SELECT pg_catalog.pg_get_functiondef(%u)",
oid);
+
+ return get_create_object_cmd_internal(query, buf);
+}
+
+/*
+ * Fetches a view definition that describes the
+ * view with the given OID. If successful, the result is stored in buf.
+ */
+static bool
+get_create_view_cmd(Oid oid, PQExpBuffer buf)
+{
+ PQExpBuffer query;
+
+ query = createPQExpBuffer();
+ printfPQExpBuffer(query, "SELECT pg_catalog.pg_get_viewdef(%u)", oid);
+
+ return get_create_object_cmd_internal(query, buf);
+}
+
+/*
+ * Unfortunately pg_get_viewdef() doesn't return "CREATE OR REPLACE"
+ * statement prefix.
+ * We need to format "CREATE" statement manually.
+ * Originally allocated buffer contents will be replaced with formatted one.
+ */
+static void
+format_create_view_cmd(char *view, PQExpBuffer buf)
+{
+ PQExpBuffer t = createPQExpBuffer();
+ printfPQExpBuffer(t,
+ "CREATE OR REPLACE VIEW %s AS\n%s\n",
+ view,
+ buf->data);
+
+ resetPQExpBuffer(buf);
+ printfPQExpBuffer(buf, "%s", t->data);
+
+ destroyPQExpBuffer(t);
+}
+
+/*
* If the given argument of \ef ends with a line number, delete the line
* number from the argument string and return it as an integer. (We need
* this kluge because we're too lazy to parse \ef's function name argument
@@ -3112,7 +3299,7 @@ get_create_function_cmd(Oid oid, PQExpBuffer buf)
* on success.
*/
static int
-strip_lineno_from_funcdesc(char *func)
+strip_lineno_from_objdesc(char *func)
{
char *c;
int lineno;
@@ -3193,3 +3380,60 @@ minimal_error_message(PGresult *res)
destroyPQExpBuffer(msg);
}
+
+static int
+count_lines_in_buf(PQExpBuffer buf)
+{
+ int lineno = 0;
+ const char *lines = buf->data;
+
+ while (*lines != '\0')
+ {
+ lineno++;
+ /* find start of next line */
+ lines = strchr(lines, '\n');
+ if (!lines)
+ break;
+ lines++;
+ }
+
+ return lineno;
+}
+
+static void
+print_with_linenumbers(FILE *output,
+ char *lines,
+ const char *header_cmp_keyword,
+ size_t header_cmp_sz)
+{
+ bool in_header = true;
+ int lineno = 0;
+
+ while (*lines != '\0')
+ {
+ char *eol;
+
+ if (in_header && strncmp(lines, header_cmp_keyword,
header_cmp_sz) == 0)
+ in_header = false;
+
+ /* increment lineno only for body's lines */
+ if (!in_header)
+ lineno++;
+
+ /* find and mark end of current line */
+ eol = strchr(lines, '\n');
+ if (eol != NULL)
+ *eol = '\0';
+
+ /* show current line as appropriate */
+ if (in_header)
+ fprintf(output, " %s\n", lines);
+ else
+ fprintf(output, "%-7d %s\n", lineno, lines);
+
+ /* advance to next line, if any */
+ if (eol == NULL)
+ break;
+ lines = ++eol;
+ }
+}
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ea05c3e..c622635 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -181,6 +181,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _("Query Buffer\n"));
fprintf(output, _(" \\e [FILE] [LINE] edit the query buffer (or
file) with external editor\n"));
fprintf(output, _(" \\ef [FUNCNAME [LINE]] edit function definition
with external editor\n"));
+ fprintf(output, _(" \\ev [VIEWNAME [LINE]] edit view definition with
external editor\n"));
fprintf(output, _(" \\p show the contents of the
query buffer\n"));
fprintf(output, _(" \\r reset (clear) the query
buffer\n"));
#ifdef USE_READLINE
@@ -238,6 +239,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dy [PATTERN] list event triggers\n"));
fprintf(output, _(" \\l[+] [PATTERN] list databases\n"));
fprintf(output, _(" \\sf[+] FUNCNAME show a function's
definition\n"));
+ fprintf(output, _(" \\sv[+] VIEWNAME show a view's
definition\n"));
fprintf(output, _(" \\z [PATTERN] same as \\dp\n"));
fprintf(output, "\n");
@@ -388,7 +390,7 @@ helpVariables(unsigned short int pager)
fprintf(output, _(" PGPASSWORD connection password (not
recommended)\n"));
fprintf(output, _(" PGPASSFILE password file name\n"));
fprintf(output, _(" PSQL_EDITOR, EDITOR, VISUAL\n"
- " editor used by
the \\e and \\ef commands\n"));
+ " editor used by
the \\e, \\ef, and \\ev commands\n"));
fprintf(output, _(" PSQL_EDITOR_LINENUMBER_ARG\n"
" how to specify a
line number when invoking the editor\n"));
fprintf(output, _(" PSQL_HISTORY alternative location for the
command history file\n"));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 750e29d..38b2efe 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -895,11 +895,11 @@ psql_completion(const char *text, int start, int end)
"\\d", "\\da", "\\db", "\\dc", "\\dC", "\\dd", "\\dD", "\\des",
"\\det", "\\deu", "\\dew", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl",
"\\dL",
"\\dn", "\\do", "\\dp", "\\drds", "\\ds", "\\dS", "\\dt",
"\\dT", "\\dv", "\\du", "\\dx",
- "\\e", "\\echo", "\\ef", "\\encoding",
+ "\\e", "\\echo", "\\ef", "\\ev", "\\encoding",
"\\f", "\\g", "\\gset", "\\h", "\\help", "\\H", "\\i", "\\ir",
"\\l",
"\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
"\\o", "\\p", "\\password", "\\prompt", "\\pset", "\\q",
"\\qecho", "\\r",
- "\\set", "\\sf", "\\t", "\\T",
+ "\\set", "\\sf", "\\sf+", "\\sv", "\\sv+", "\\t", "\\T",
"\\timing", "\\unset", "\\x", "\\w", "\\watch", "\\z", "\\!",
NULL
};
@@ -3776,6 +3776,8 @@ psql_completion(const char *text, int start, int end)
else if (strcmp(prev_wd, "\\ef") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+ else if (strcmp(prev_wd, "\\ev") == 0)
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
else if (strcmp(prev_wd, "\\encoding") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_encodings);
@@ -3890,6 +3892,8 @@ psql_completion(const char *text, int start, int end)
}
else if (strcmp(prev_wd, "\\sf") == 0 || strcmp(prev_wd, "\\sf+") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+ else if (strcmp(prev_wd, "\\sv") == 0 || strcmp(prev_wd, "\\sv+") == 0)
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
else if (strcmp(prev_wd, "\\cd") == 0 ||
strcmp(prev_wd, "\\e") == 0 || strcmp(prev_wd,
"\\edit") == 0 ||
strcmp(prev_wd, "\\g") == 0 ||
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
