Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5b66de3

Browse files
committed
psql: Add command to use extended query protocol
This adds a new psql command \bind that sets query parameters andcauses the next query to be sent using the extended query protocol.Example: SELECT $1, $2 \bind 'foo' 'bar' \gThis may be useful for psql scripting, but one of the main purposes isalso to be able to test various aspects of the extended query protocolfrom psql and to write tests more easily.Reviewed-by: Corey Huinker <corey.huinker@gmail.com>Discussion:https://www.postgresql.org/message-id/flat/e8dd1cd5-0e04-3598-0518-a605159fe314@enterprisedb.com
1 parenta9e9a9f commit5b66de3

File tree

8 files changed

+137
-1
lines changed

8 files changed

+137
-1
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -879,6 +879,42 @@ testdb=&gt;
879879
</listitem>
880880
</varlistentry>
881881

882+
<varlistentry>
883+
<term><literal>\bind</literal> [ <replaceable class="parameter">parameter</replaceable> ] ... </term>
884+
885+
<listitem>
886+
<para>
887+
Sets query parameters for the next query execution, with the
888+
specified parameters passed for any parameter placeholders
889+
(<literal>$1</literal> etc.).
890+
</para>
891+
892+
<para>
893+
Example:
894+
<programlisting>
895+
INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
896+
</programlisting>
897+
</para>
898+
899+
<para>
900+
This also works for query-execution commands besides
901+
<literal>\g</literal>, such as <literal>\gx</literal> and
902+
<literal>\gset</literal>.
903+
</para>
904+
905+
<para>
906+
This command causes the extended query protocol (see <xref
907+
linkend="protocol-query-concepts"/>) to be used, unlike normal
908+
<application>psql</application> operation, which uses the simple
909+
query protocol. So this command can be useful to test the extended
910+
query protocol from psql. (The extended query protocol is used even
911+
if the query has no parameters and this command specifies zero
912+
parameters.) This command affects only the next query executed; all
913+
subsequent queries will use the simple query protocol by default.
914+
</para>
915+
</listitem>
916+
</varlistentry>
917+
882918
<varlistentry>
883919
<term><literal>\c</literal> or <literal>\connect [ -reuse-previous=<replaceable class="parameter">on|off</replaceable> ] [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] | <replaceable class="parameter">conninfo</replaceable> ]</literal></term>
884920
<listitem>

‎src/bin/psql/command.c

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -63,6 +63,7 @@ static backslashResult exec_command(const char *cmd,
6363
PQExpBufferquery_buf,
6464
PQExpBufferprevious_buf);
6565
staticbackslashResultexec_command_a(PsqlScanStatescan_state,boolactive_branch);
66+
staticbackslashResultexec_command_bind(PsqlScanStatescan_state,boolactive_branch);
6667
staticbackslashResultexec_command_C(PsqlScanStatescan_state,boolactive_branch);
6768
staticbackslashResultexec_command_connect(PsqlScanStatescan_state,boolactive_branch);
6869
staticbackslashResultexec_command_cd(PsqlScanStatescan_state,boolactive_branch,
@@ -308,6 +309,8 @@ exec_command(const char *cmd,
308309

309310
if (strcmp(cmd,"a")==0)
310311
status=exec_command_a(scan_state,active_branch);
312+
elseif (strcmp(cmd,"bind")==0)
313+
status=exec_command_bind(scan_state,active_branch);
311314
elseif (strcmp(cmd,"C")==0)
312315
status=exec_command_C(scan_state,active_branch);
313316
elseif (strcmp(cmd,"c")==0||strcmp(cmd,"connect")==0)
@@ -453,6 +456,40 @@ exec_command_a(PsqlScanState scan_state, bool active_branch)
453456
returnsuccess ?PSQL_CMD_SKIP_LINE :PSQL_CMD_ERROR;
454457
}
455458

459+
/*
460+
* \bind -- set query parameters
461+
*/
462+
staticbackslashResult
463+
exec_command_bind(PsqlScanStatescan_state,boolactive_branch)
464+
{
465+
backslashResultstatus=PSQL_CMD_SKIP_LINE;
466+
467+
if (active_branch)
468+
{
469+
char*opt;
470+
intnparams=0;
471+
intnalloc=0;
472+
473+
pset.bind_params=NULL;
474+
475+
while ((opt=psql_scan_slash_option(scan_state,OT_NORMAL,NULL, false)))
476+
{
477+
nparams++;
478+
if (nparams>nalloc)
479+
{
480+
nalloc=nalloc ?nalloc*2 :1;
481+
pset.bind_params=pg_realloc_array(pset.bind_params,char*,nalloc);
482+
}
483+
pset.bind_params[nparams-1]=pg_strdup(opt);
484+
}
485+
486+
pset.bind_nparams=nparams;
487+
pset.bind_flag= true;
488+
}
489+
490+
returnstatus;
491+
}
492+
456493
/*
457494
* \C -- override table title (formerly change HTML caption)
458495
*/

‎src/bin/psql/common.c

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1220,6 +1220,16 @@ SendQuery(const char *query)
12201220
pset.gsavepopt=NULL;
12211221
}
12221222

1223+
/* clean up after \bind */
1224+
if (pset.bind_flag)
1225+
{
1226+
for (i=0;i<pset.bind_nparams;i++)
1227+
free(pset.bind_params[i]);
1228+
free(pset.bind_params);
1229+
pset.bind_params=NULL;
1230+
pset.bind_flag= false;
1231+
}
1232+
12231233
/* reset \gset trigger */
12241234
if (pset.gset_prefix)
12251235
{
@@ -1397,7 +1407,10 @@ ExecQueryAndProcessResults(const char *query,
13971407
if (timing)
13981408
INSTR_TIME_SET_CURRENT(before);
13991409

1400-
success=PQsendQuery(pset.db,query);
1410+
if (pset.bind_flag)
1411+
success=PQsendQueryParams(pset.db,query,pset.bind_nparams,NULL, (constchar*const*)pset.bind_params,NULL,NULL,0);
1412+
else
1413+
success=PQsendQuery(pset.db,query);
14011414

14021415
if (!success)
14031416
{

‎src/bin/psql/help.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -189,6 +189,7 @@ slashUsage(unsigned short int pager)
189189
initPQExpBuffer(&buf);
190190

191191
HELP0("General\n");
192+
HELP0(" \\bind [PARAM]... set query parameters\n");
192193
HELP0(" \\copyright show PostgreSQL usage and distribution terms\n");
193194
HELP0(" \\crosstabview [COLUMNS] execute query and display result in crosstab\n");
194195
HELP0(" \\errverbose show most recent error message at maximum verbosity\n");

‎src/bin/psql/settings.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -96,6 +96,9 @@ typedef struct _psqlSettings
9696
char*gset_prefix;/* one-shot prefix argument for \gset */
9797
boolgdesc_flag;/* one-shot request to describe query result */
9898
boolgexec_flag;/* one-shot request to execute query result */
99+
boolbind_flag;/* one-shot request to use extended query protocol */
100+
intbind_nparams;/* number of parameters */
101+
char**bind_params;/* parameters for extended query protocol call */
99102
boolcrosstab_flag;/* one-shot request to crosstab result */
100103
char*ctv_args[4];/* \crosstabview arguments */
101104

‎src/bin/psql/tab-complete.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1680,6 +1680,7 @@ psql_completion(const char *text, int start, int end)
16801680
/* psql's backslash commands. */
16811681
staticconstchar*constbackslash_commands[]= {
16821682
"\\a",
1683+
"\\bind",
16831684
"\\connect","\\conninfo","\\C","\\cd","\\copy",
16841685
"\\copyright","\\crosstabview",
16851686
"\\d","\\da","\\dA","\\dAc","\\dAf","\\dAo","\\dAp",

‎src/test/regress/expected/psql.out

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -98,6 +98,37 @@ two | 2
9898
1 | 2
9999
(1 row)
100100

101+
-- \bind (extended query protocol)
102+
SELECT 1 \bind \g
103+
?column?
104+
----------
105+
1
106+
(1 row)
107+
108+
SELECT $1 \bind 'foo' \g
109+
?column?
110+
----------
111+
foo
112+
(1 row)
113+
114+
SELECT $1, $2 \bind 'foo' 'bar' \g
115+
?column? | ?column?
116+
----------+----------
117+
foo | bar
118+
(1 row)
119+
120+
-- errors
121+
-- parse error
122+
SELECT foo \bind \g
123+
ERROR: column "foo" does not exist
124+
LINE 1: SELECT foo
125+
^
126+
-- tcop error
127+
SELECT 1 \; SELECT 2 \bind \g
128+
ERROR: cannot insert multiple commands into a prepared statement
129+
-- bind error
130+
SELECT $1, $2 \bind 'foo' \g
131+
ERROR: bind message supplies 1 parameters, but prepared statement "" requires 2
101132
-- \gset
102133
select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
103134
\echo :pref01_test01 :pref01_test02 :pref01_test03

‎src/test/regress/sql/psql.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -45,6 +45,20 @@ SELECT 1 as one, 2 as two \g (format=csv csv_fieldsep='\t')
4545
SELECT1as one,2as two \gx (title='foo bar')
4646
\g
4747

48+
-- \bind (extended query protocol)
49+
50+
SELECT1 \bind \g
51+
SELECT $1 \bind'foo' \g
52+
SELECT $1, $2 \bind'foo''bar' \g
53+
54+
-- errors
55+
-- parse error
56+
SELECT foo \bind \g
57+
-- tcop error
58+
SELECT1 \;SELECT2 \bind \g
59+
-- bind error
60+
SELECT $1, $2 \bind'foo' \g
61+
4862
-- \gset
4963

5064
select10as test01,20as test02,'Hello'as test03 \gset pref01_

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp