This adds a new psql command \gp that works like \g (or semicolon) but
uses the extended query protocol. Parameters can also be passed, like
SELECT $1, $2 \gp 'foo' 'bar'
I have two main purposes for this:
One, for transparent column encryption [0], we need a way to pass
protocol-level parameters. The present patch in the [0] thread uses a
command \gencr, but based on feedback and further thinking, a
general-purpose command seems better.
Two, for testing the extended query protocol from psql. For example,
for the dynamic result sets patch [1], I have several ad-hoc libpq test
programs lying around, which would be cumbersome to integrate into the
patch. With psql support like proposed here, it would be very easy to
integrate a few equivalent tests.
Perhaps this would also be useful for general psql scripting.
[0]: https://commitfest.postgresql.org/40/3718/
[1]: https://commitfest.postgresql.org/40/2911/
From 3f4bf4a68c2edd57c7bf4c4935bad50ea0f528b7 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Fri, 28 Oct 2022 08:29:46 +0200
Subject: [PATCH] psql: Add command to use extended query protocol
This adds a new psql command \gp that works like \g (or semicolon) but
uses the extended query protocol. Parameters can also be passed, like
SELECT $1, $2 \gp 'foo' 'bar'
This may be useful for psql scripting, but one of the main purposes is
also to be able to test various aspects of the extended query protocol
from psql and to write tests more easily.
---
doc/src/sgml/ref/psql-ref.sgml | 27 +++++++++++++++++++++
src/bin/psql/command.c | 39 ++++++++++++++++++++++++++++++
src/bin/psql/common.c | 15 +++++++++++-
src/bin/psql/help.c | 1 +
src/bin/psql/settings.h | 3 +++
src/bin/psql/tab-complete.c | 2 +-
src/test/regress/expected/psql.out | 31 ++++++++++++++++++++++++
src/test/regress/sql/psql.sql | 14 +++++++++++
8 files changed, 130 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 9494f28063ad..51b33fd3b80c 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2323,6 +2323,33 @@ <title>Meta-Commands</title>
</varlistentry>
+ <varlistentry>
+ <term><literal>\gp</literal> [ <replaceable
class="parameter">parameter</replaceable> ] ... </term>
+
+ <listitem>
+ <para>
+ Sends the current query buffer to the server for execution, as with
+ <literal>\g</literal>, with the specified parameters passed for any
+ parameter placeholders (<literal>$1</literal> etc.).
+ </para>
+
+ <para>
+ Example:
+<programlisting>
+INSERT INTO tbl1 VALUES ($1, $2) \gp 'first value' 'second value'
+</programlisting>
+ </para>
+
+ <para>
+ This command uses the extended query protocol (see <xref
+ linkend="protocol-query-concepts"/>), unlike <literal>\g</literal>,
+ which uses the simple query protocol. So this command can be useful
+ to test the extended query protocol from psql.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
<varlistentry>
<term><literal>\gset [ <replaceable
class="parameter">prefix</replaceable> ]</literal></term>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ab613dd49e0a..0e760eda1f3e 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -101,6 +101,7 @@ static backslashResult exec_command_gdesc(PsqlScanState
scan_state, bool active_
static backslashResult exec_command_getenv(PsqlScanState scan_state, bool
active_branch,
const char *cmd);
static backslashResult exec_command_gexec(PsqlScanState scan_state, bool
active_branch);
+static backslashResult exec_command_gp(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);
static backslashResult exec_command_html(PsqlScanState scan_state, bool
active_branch);
@@ -354,6 +355,8 @@ exec_command(const char *cmd,
status = exec_command_getenv(scan_state, active_branch, cmd);
else if (strcmp(cmd, "gexec") == 0)
status = exec_command_gexec(scan_state, active_branch);
+ else if (strcmp(cmd, "gp") == 0)
+ status = exec_command_gp(scan_state, active_branch);
else if (strcmp(cmd, "gset") == 0)
status = exec_command_gset(scan_state, active_branch);
else if (strcmp(cmd, "h") == 0 || strcmp(cmd, "help") == 0)
@@ -1546,6 +1549,42 @@ exec_command_gexec(PsqlScanState scan_state, bool
active_branch)
return status;
}
+/*
+ * \gp -- send the current query with parameters
+ */
+static backslashResult
+exec_command_gp(PsqlScanState scan_state, bool active_branch)
+{
+ backslashResult status = PSQL_CMD_SKIP_LINE;
+
+ if (active_branch)
+ {
+ char *opt;
+ int nparams = 0;
+ int nalloc = 0;
+
+ pset.gp_params = NULL;
+
+ while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL,
NULL, false)))
+ {
+ nparams++;
+ if (nparams > nalloc)
+ {
+ nalloc = nalloc ? nalloc * 2 : 1;
+ pset.gp_params =
pg_realloc_array(pset.gp_params, char *, nalloc);
+ }
+ pset.gp_params[nparams - 1] = pg_strdup(opt);
+ }
+
+ pset.gp_nparams = nparams;
+ pset.gp_flag = true;
+
+ status = PSQL_CMD_SEND;
+ }
+
+ return status;
+}
+
/*
* \gset [prefix] -- send query and store result into variables
*/
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 864f195992f5..8c37bccec30f 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1220,6 +1220,16 @@ SendQuery(const char *query)
pset.gsavepopt = NULL;
}
+ /* clean up after \gp */
+ if (pset.gp_flag)
+ {
+ for (i = 0; i < pset.gp_nparams; i++)
+ free(pset.gp_params[i]);
+ free(pset.gp_params);
+ pset.gp_params = NULL;
+ pset.gp_flag = false;
+ }
+
/* reset \gset trigger */
if (pset.gset_prefix)
{
@@ -1397,7 +1407,10 @@ ExecQueryAndProcessResults(const char *query,
if (timing)
INSTR_TIME_SET_CURRENT(before);
- success = PQsendQuery(pset.db, query);
+ if (pset.gp_flag)
+ success = PQsendQueryParams(pset.db, query, pset.gp_nparams,
NULL, (const char * const *) pset.gp_params, NULL, NULL, 0);
+ else
+ success = PQsendQuery(pset.db, query);
if (!success)
{
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index f8ce1a07060d..bae7707b1e49 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -196,6 +196,7 @@ slashUsage(unsigned short int pager)
" \\g with no arguments is equivalent
to a semicolon\n");
HELP0(" \\gdesc describe result of query, without
executing it\n");
HELP0(" \\gexec execute query, then execute each value
in its result\n");
+ HELP0(" \\gp [PARAM]... execute query using extended
protocol\n");
HELP0(" \\gset [PREFIX] execute query and store result in psql
variables\n");
HELP0(" \\gx [(OPTIONS)] [FILE] as \\g, but forces expanded output
mode\n");
HELP0(" \\q quit psql\n");
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 2399cffa3fba..9e283566543a 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -96,6 +96,9 @@ typedef struct _psqlSettings
char *gset_prefix; /* one-shot prefix argument for \gset */
bool gdesc_flag; /* one-shot request to describe
query result */
bool gexec_flag; /* one-shot request to execute
query result */
+ bool gp_flag; /* one-shot request to use
extended query protocol */
+ int gp_nparams; /* number of parameters
*/
+ char **gp_params; /* parameters for extended query
protocol call */
bool crosstab_flag; /* one-shot request to crosstab result
*/
char *ctv_args[4]; /* \crosstabview arguments */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index a64571215b33..e16c8edd1c8e 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1692,7 +1692,7 @@ psql_completion(const char *text, int start, int end)
"\\echo", "\\edit", "\\ef", "\\elif", "\\else", "\\encoding",
"\\endif", "\\errverbose", "\\ev",
"\\f",
- "\\g", "\\gdesc", "\\getenv", "\\gexec", "\\gset", "\\gx",
+ "\\g", "\\gdesc", "\\getenv", "\\gexec", "\\gp", "\\gset",
"\\gx",
"\\help", "\\html",
"\\if", "\\include", "\\include_relative", "\\ir",
"\\list", "\\lo_import", "\\lo_export", "\\lo_list",
"\\lo_unlink",
diff --git a/src/test/regress/expected/psql.out
b/src/test/regress/expected/psql.out
index a7f5700edc12..fd2532c8628f 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -98,6 +98,37 @@ two | 2
1 | 2
(1 row)
+-- \gp (extended query protocol)
+SELECT 1 \gp
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT $1 \gp 'foo'
+ ?column?
+----------
+ foo
+(1 row)
+
+SELECT $1, $2 \gp 'foo' 'bar'
+ ?column? | ?column?
+----------+----------
+ foo | bar
+(1 row)
+
+-- errors
+-- parse error
+SELECT foo \gp
+ERROR: column "foo" does not exist
+LINE 1: SELECT foo
+ ^
+-- tcop error
+SELECT 1 \; SELECT 2 \gp
+ERROR: cannot insert multiple commands into a prepared statement
+-- bind error
+SELECT $1, $2 \gp 'foo'
+ERROR: bind message supplies 1 parameters, but prepared statement "" requires
2
-- \gset
select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
\echo :pref01_test01 :pref01_test02 :pref01_test03
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 1149c6a839ef..c75b3c460b20 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -45,6 +45,20 @@
SELECT 1 as one, 2 as two \gx (title='foo bar')
\g
+-- \gp (extended query protocol)
+
+SELECT 1 \gp
+SELECT $1 \gp 'foo'
+SELECT $1, $2 \gp 'foo' 'bar'
+
+-- errors
+-- parse error
+SELECT foo \gp
+-- tcop error
+SELECT 1 \; SELECT 2 \gp
+-- bind error
+SELECT $1, $2 \gp 'foo'
+
-- \gset
select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
--
2.37.3
