2017-05-09 18:15 GMT+02:00 Fabien COELHO <coe...@cri.ensmp.fr>:
>
> Hello Pavel,
>
> Patch applies cleanly and compiles.
>>>
>>
> Idem for v2. "make check" ok. Tests look good.
>
> I would suggest some rewording, maybe:
>>>
>>> "Show the description of the result of the current query buffer without
>>> actually executing it, by considering it a prepared statement."
>>>
>>> done
>>
>
> Ok. If some native English speaker can clarify the sentence further, or
> imprive it anyway, thanks in advance!
>
> SELECT $1 AS unknown_type \gdesc
>>>
>>
>> It is not unknown type - the default placeholder type is text
>>
>
> Indeed. I really meant something like:
>
> calvin=# SELECT $1 + $2 \gdesc
> ERROR: operator is not unique: unknown + unknown
> ...
>
> More comments:
>
> I propose that the help message could be "describe result of query without
> executing it".
>
done
>
> I found an issue. \gdesk fails when the command does not return a result:
>
> calvin=# TRUNCATE pgbench_history \gdesc
> ERROR: syntax error at or near ")"
> LINE 2: (VALUES ) s (name, tp, tpm)
>
> I guess the issue is that PQdescribePrepared returns an empty description,
> which is fine, but then the second query should be skipped, and some
> message should be output instead, like "no result" or whatever...
>
> This need fixing, and a corresponding test should be added.
>
it is little bit worse. I cannot to distinguish between SELECT\gdesc and
TRUNCATE xxx\gdesc . All are valid commands and produce empty result, so
result of \gdesc command should be empty result too.
postgres=# truncate table xx\gdesc
┌──────┬──────┐
│ Name │ Type │
╞══════╪══════╡
└──────┴──────┘
(0 rows)
postgres=# select \gdesc
┌──────┬──────┐
│ Name │ Type │
╞══════╪══════╡
└──────┴──────┘
(0 rows)
> Also I would suggest to add a \g after the first test, which would execute
> the current buffer after its description, to show that the current buffer
> does indeed hold the query:
>
> calvin=# SELECT 1 as one, ... \gdesc \g
> -- one | int
> -- ...
> -- 1 | ...
>
>
done
Regards
Pavel
> --
> Fabien.
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 3b86612..2e46f4c 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1957,6 +1957,16 @@ Tue Oct 26 21:40:57 CEST 1999
<varlistentry>
+ <term><literal>\gdesc</literal></term>
+ <listitem>
+ <para>
+ Show the description of the result of the current query buffer without
+ actually executing it, by considering it a prepared statement.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>\gexec</literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index b3263a9..a1c8e1d 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -88,6 +88,7 @@ static backslashResult exec_command_errverbose(PsqlScanState scan_state, bool ac
static backslashResult exec_command_f(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_g(PsqlScanState scan_state, bool active_branch,
const char *cmd);
+static backslashResult exec_command_gdesc(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_gexec(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_gset(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_help(PsqlScanState scan_state, bool active_branch);
@@ -337,6 +338,8 @@ exec_command(const char *cmd,
status = exec_command_f(scan_state, active_branch);
else if (strcmp(cmd, "g") == 0 || strcmp(cmd, "gx") == 0)
status = exec_command_g(scan_state, active_branch, cmd);
+ else if (strcmp(cmd, "gdesc") == 0)
+ status = exec_command_gdesc(scan_state, active_branch);
else if (strcmp(cmd, "gexec") == 0)
status = exec_command_gexec(scan_state, active_branch);
else if (strcmp(cmd, "gset") == 0)
@@ -1328,6 +1331,25 @@ exec_command_g(PsqlScanState scan_state, bool active_branch, const char *cmd)
}
/*
+ * \gdesc -- describe query result
+ */
+static backslashResult
+exec_command_gdesc(PsqlScanState scan_state, bool active_branch)
+{
+ backslashResult status = PSQL_CMD_SKIP_LINE;
+
+ if (active_branch)
+ {
+ pset.gdesc_flag = true;
+ status = PSQL_CMD_SEND;
+ }
+ else
+ ignore_slash_filepipe(scan_state);
+
+ return status;
+}
+
+/*
* \gexec -- send query and execute each field of result
*/
static backslashResult
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index a2f1259..529034f 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1323,7 +1323,87 @@ SendQuery(const char *query)
}
}
- if (pset.fetch_count <= 0 || pset.gexec_flag ||
+ if (pset.gdesc_flag)
+ {
+ /*
+ * Unnamed prepared statement is used. Is not possible to
+ * create any unnamed prepared statement from psql user space,
+ * so there should not be any conflict. In this moment is not
+ * possible to deallocate this prepared statement, so it should
+ * to live to end of session or to another \gdesc call.
+ */
+ results = PQprepare(pset.db, "", query, 0, NULL);
+ if (PQresultStatus(results) != PGRES_COMMAND_OK)
+ {
+ psql_error("%s", PQerrorMessage(pset.db));
+ ClearOrSaveResult(results);
+ ResetCancelConn();
+ goto sendquery_cleanup;
+ }
+ PQclear(results);
+
+ results = PQdescribePrepared(pset.db, "");
+ OK = ProcessResult(&results);
+ if (OK && results)
+ {
+ PQExpBufferData buf;
+ int i;
+
+ initPQExpBuffer(&buf);
+
+ /*
+ * The description can do empty result (when some commands doesn't
+ * produce result or another corner case "SELECT;". In this case
+ * the created query should to generate empty result too. The simply
+ * solution is special always filtered row, that ensures so query
+ * will be valid every time.
+ */
+ printfPQExpBuffer(&buf,
+ "SELECT name AS \"Name\", pg_catalog.format_type(tp, tpm) AS \"Type\" FROM\n"
+ " (VALUES ('',-1, -1)");
+
+ for(i = 0; i< PQnfields(results); i++)
+ {
+ char *name;
+ char *escname;
+ size_t name_length;
+
+ appendPQExpBufferStr(&buf, ",");
+
+ name = PQfname(results, i);
+ name_length = strlen(name);
+ escname = PQescapeLiteral(pset.db, name, name_length);
+
+ if (escname == NULL)
+ {
+ psql_error("%s", PQerrorMessage(pset.db));
+ PQclear(results);
+ termPQExpBuffer(&buf);
+ goto sendquery_cleanup;
+ }
+
+ appendPQExpBuffer(&buf, "(%s, %d, %d)",
+ escname, PQftype(results,i), PQfmod(results,i));
+ PQfreemem(escname);
+ }
+
+ appendPQExpBuffer(&buf,") s (name, tp, tpm) WHERE tp >= 0");
+ PQclear(results);
+
+ results = PQexec(pset.db, buf.data);
+ OK = ProcessResult(&results);
+
+ if (OK && results)
+ OK = PrintQueryResults(results);
+
+ termPQExpBuffer(&buf);
+ }
+
+ ClearOrSaveResult(results);
+ ResetCancelConn();
+ results = NULL; /* PQclear(NULL) does nothing */
+ }
+ else if (pset.fetch_count <= 0 || pset.gexec_flag ||
pset.crosstab_flag || !is_select_command(query))
{
/* Default fetch-it-all-and-print mode */
@@ -1478,6 +1558,9 @@ sendquery_cleanup:
pset.ctv_args[i] = NULL;
}
+ /* reset \gdesc trigger */
+ pset.gdesc_flag = false;
+
return OK;
}
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ac43522..68dfc10 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -167,12 +167,13 @@ slashUsage(unsigned short int pager)
* Use "psql --help=commands | wc" to count correctly. It's okay to count
* the USE_READLINE line even in builds without that.
*/
- output = PageOutput(122, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(123, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("General\n"));
fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n"));
fprintf(output, _(" \\errverbose show most recent error message at maximum verbosity\n"));
fprintf(output, _(" \\g [FILE] or ; execute query (and send results to file or |pipe)\n"));
+ fprintf(output, _(" \\gdesc describe result of query without executing it\n"));
fprintf(output, _(" \\gx [FILE] as \\g, but forces expanded output mode\n"));
fprintf(output, _(" \\gexec execute query, then execute each value in its result\n"));
fprintf(output, _(" \\gset [PREFIX] execute query and store results in psql variables\n"));
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 70ff181..a175912 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -95,6 +95,7 @@ typedef struct _psqlSettings
char *gset_prefix; /* one-shot prefix argument for \gset */
bool gexec_flag; /* one-shot flag to execute query's results */
bool crosstab_flag; /* one-shot request to crosstab results */
+ bool gdesc_flag; /* one-shot request to describe query */
char *ctv_args[4]; /* \crosstabview arguments */
bool notty; /* stdin or stdout is not a tty (as determined
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 183fc37..e6a0b4a 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1410,7 +1410,7 @@ psql_completion(const char *text, int start, int end)
"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\drds", "\\ds", "\\dS",
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
"\\e", "\\echo", "\\ef", "\\encoding", "\\errverbose", "\\ev",
- "\\f", "\\g", "\\gexec", "\\gset", "\\gx", "\\h", "\\help", "\\H",
+ "\\f", "\\g", "\\gdesc", "\\gexec", "\\gset", "\\gx", "\\h", "\\help", "\\H",
"\\i", "\\ir", "\\l", "\\lo_import", "\\lo_export", "\\lo_list",
"\\lo_unlink", "\\o", "\\p", "\\password", "\\prompt", "\\pset", "\\q",
"\\qecho", "\\r", "\\s", "\\set", "\\setenv", "\\sf", "\\sv", "\\t",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index d602aee..bf604fb 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -2964,3 +2964,62 @@ SELECT 3
UNION SELECT 4
UNION SELECT 5
ORDER BY 1;
+-- gdesc tests
+SELECT
+ NULL AS zero,
+ 1 AS one,
+ 2.0 AS two,
+ 'three' AS three,
+ $1 AS four,
+ sin($2) as five,
+ CURRENT_DATE AS now
+\gdesc
+ Name | Type
+-------+------------------
+ zero | text
+ one | integer
+ two | numeric
+ three | text
+ four | text
+ five | double precision
+ now | date
+(7 rows)
+
+PREPARE test AS SELECT 1 AS first;
+EXECUTE test \gdesc
+ Name | Type
+-------+---------
+ first | integer
+(1 row)
+
+-- should fail - syntax error
+SELECT 1 + \gdesc
+ERROR: syntax error at end of input
+LINE 1: SELECT 1 +
+ ^
+-- empty results
+SELECT \gdesc
+ Name | Type
+------+------
+(0 rows)
+
+CREATE TABLE bububu(a int)\gdesc
+ Name | Type
+------+------
+(0 rows)
+
+-- query buffer should not be broken
+SELECT 1 AS x, 'Hello' AS y
+\gdesc
+ Name | Type
+------+---------
+ x | integer
+ y | text
+(2 rows)
+
+\g
+ x | y
+---+-------
+ 1 | Hello
+(1 row)
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index b56a05f..caaa64d 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -560,3 +560,29 @@ UNION SELECT 5
ORDER BY 1;
\r
\p
+
+-- gdesc tests
+SELECT
+ NULL AS zero,
+ 1 AS one,
+ 2.0 AS two,
+ 'three' AS three,
+ $1 AS four,
+ sin($2) as five,
+ CURRENT_DATE AS now
+\gdesc
+
+PREPARE test AS SELECT 1 AS first;
+EXECUTE test \gdesc
+
+-- should fail - syntax error
+SELECT 1 + \gdesc
+
+-- empty results
+SELECT \gdesc
+CREATE TABLE bububu(a int)\gdesc
+
+-- query buffer should not be broken
+SELECT 1 AS x, 'Hello' AS y
+\gdesc
+\g
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
