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

Commitd55322b

Browse files
committed
psql: Add more meta-commands able to use the extended protocol
Currently, only unnamed prepared statement are supported by psql withthe meta-command \bind. With only this command, it is not possible totest named statement creation, execution or close through the extendedprotocol.This commit introduces three additional commands:* \parse creates a prepared statement using the extended protocol,acting as a wrapper of libpq's PQsendPrepare().* \bind_named binds and executes an existing prepared statement usingthe extended protocol, for PQsendQueryPrepared().* \close closes an existing prepared statement using the extendedprotocol, for PQsendClosePrepared().This is going to be useful to add regression tests for the extendedquery protocol, and I have some plans for that on separate threads.Note that \bind relies on PQsendQueryParams().The code of psql is refactored so as bind_flag is replaced by an enum in_psqlSettings that tracks the type of libpq routine to execute, based onthe meta-command involved, with the default being PQsendQuery(). Thisrefactoring piece has been written by me, while Anthonin has implementedthe rest.Author: Anthonin Bonnefoy, Michael PaquierReviewed-by: Aleksander Alekseev, Jelte Fennema-NioDiscussion:https://postgr.es/m/CAO6_XqpSq0Q0kQcVLCbtagY94V2GxNP3zCnR6WnOM8WqXPK4nw@mail.gmail.com
1 parenta36aa22 commitd55322b

File tree

9 files changed

+369
-19
lines changed

9 files changed

+369
-19
lines changed

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

Lines changed: 90 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -917,6 +917,36 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
917917
</listitem>
918918
</varlistentry>
919919

920+
<varlistentry id="app-psql-meta-command-bind-named">
921+
<term><literal>\bind_named</literal> <replaceable class="parameter">statement_name</replaceable> [ <replaceable class="parameter">parameter</replaceable> ] ... </term>
922+
923+
<listitem>
924+
<para>
925+
<literal>\bind_named</literal> is equivalent to <literal>\bind</literal>,
926+
except that it takes the name of an existing prepared statement as
927+
first parameter. An empty string denotes the unnamed prepared
928+
statement.
929+
</para>
930+
931+
<para>
932+
Example:
933+
<programlisting>
934+
INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
935+
\bind_named stmt1 'first value' 'second value' \g
936+
</programlisting>
937+
</para>
938+
939+
<para>
940+
This command causes the extended query protocol (see
941+
<xref linkend="protocol-query-concepts"/>) to be used, unlike normal
942+
<application>psql</application> operation, which uses the simple
943+
query protocol. So this command can be useful to test the extended
944+
query protocol from <application>psql</application>.
945+
</para>
946+
947+
</listitem>
948+
</varlistentry>
949+
920950
<varlistentry id="app-psql-meta-command-c-lc">
921951
<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>
922952
<listitem>
@@ -1038,6 +1068,35 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
10381068
</listitem>
10391069
</varlistentry>
10401070

1071+
<varlistentry id="app-psql-meta-command-close">
1072+
<term><literal>\close</literal> <replaceable class="parameter">prepared_statement_name</replaceable></term>
1073+
1074+
<listitem>
1075+
<para>
1076+
Closes the specified prepared statement. An empty string denotes the
1077+
unnamed prepared statement. If no prepared statement exists with this
1078+
name, the operation is a no-op.
1079+
</para>
1080+
1081+
<para>
1082+
Example:
1083+
<programlisting>
1084+
SELECT $1 \parse stmt1
1085+
\close stmt1
1086+
</programlisting>
1087+
</para>
1088+
1089+
<para>
1090+
This command causes the extended query protocol to be used,
1091+
unlike normal <application>psql</application> operation, which
1092+
uses the simple query protocol. So this command can be useful
1093+
to test the extended query protocol from
1094+
<application>psql</application>.
1095+
</para>
1096+
1097+
</listitem>
1098+
</varlistentry>
1099+
10411100
<varlistentry id="app-psql-meta-commands-copy">
10421101
<term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] }
10431102
<literal>from</literal>
@@ -2780,6 +2839,37 @@ lo_import 152801
27802839
</listitem>
27812840
</varlistentry>
27822841

2842+
<varlistentry id="app-psql-meta-command-parse">
2843+
<term><literal>\parse <replaceable class="parameter">statement_name</replaceable></literal></term>
2844+
<listitem>
2845+
<para>
2846+
Creates a prepared statement from the current query buffer, based on
2847+
the name of a destination prepared-statement object. An empty string
2848+
denotes the unnamed prepared statement.
2849+
</para>
2850+
2851+
<para>
2852+
Example:
2853+
<programlisting>
2854+
SELECT $1 \parse stmt1
2855+
</programlisting>
2856+
</para>
2857+
2858+
<para>
2859+
This command causes the extended query protocol to be used, unlike
2860+
normal <application>psql</application> operation, which uses the
2861+
simple query protocol. A
2862+
<xref linkend="protocol-message-formats-Parse"/>
2863+
message will be issued by this command so it can be useful to
2864+
test the extended query protocol from
2865+
<application>psql</application>. This command affects only the next
2866+
query executed; all subsequent queries will use the simple query
2867+
protocol by default.
2868+
</para>
2869+
2870+
</listitem>
2871+
</varlistentry>
2872+
27832873
<varlistentry id="app-psql-meta-command-password">
27842874
<term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term>
27852875
<listitem>

‎src/bin/psql/command.c

Lines changed: 129 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -64,10 +64,14 @@ static backslashResult exec_command(const char *cmd,
6464
PQExpBufferprevious_buf);
6565
staticbackslashResultexec_command_a(PsqlScanStatescan_state,boolactive_branch);
6666
staticbackslashResultexec_command_bind(PsqlScanStatescan_state,boolactive_branch);
67+
staticbackslashResultexec_command_bind_named(PsqlScanStatescan_state,boolactive_branch,
68+
constchar*cmd);
6769
staticbackslashResultexec_command_C(PsqlScanStatescan_state,boolactive_branch);
6870
staticbackslashResultexec_command_connect(PsqlScanStatescan_state,boolactive_branch);
6971
staticbackslashResultexec_command_cd(PsqlScanStatescan_state,boolactive_branch,
7072
constchar*cmd);
73+
staticbackslashResultexec_command_close(PsqlScanStatescan_state,boolactive_branch,
74+
constchar*cmd);
7175
staticbackslashResultexec_command_conninfo(PsqlScanStatescan_state,boolactive_branch);
7276
staticbackslashResultexec_command_copy(PsqlScanStatescan_state,boolactive_branch);
7377
staticbackslashResultexec_command_copyright(PsqlScanStatescan_state,boolactive_branch);
@@ -116,6 +120,8 @@ static backslashResult exec_command_lo(PsqlScanState scan_state, bool active_bra
116120
staticbackslashResultexec_command_out(PsqlScanStatescan_state,boolactive_branch);
117121
staticbackslashResultexec_command_print(PsqlScanStatescan_state,boolactive_branch,
118122
PQExpBufferquery_buf,PQExpBufferprevious_buf);
123+
staticbackslashResultexec_command_parse(PsqlScanStatescan_state,boolactive_branch,
124+
constchar*cmd);
119125
staticbackslashResultexec_command_password(PsqlScanStatescan_state,boolactive_branch);
120126
staticbackslashResultexec_command_prompt(PsqlScanStatescan_state,boolactive_branch,
121127
constchar*cmd);
@@ -312,12 +318,16 @@ exec_command(const char *cmd,
312318
status=exec_command_a(scan_state,active_branch);
313319
elseif (strcmp(cmd,"bind")==0)
314320
status=exec_command_bind(scan_state,active_branch);
321+
elseif (strcmp(cmd,"bind_named")==0)
322+
status=exec_command_bind_named(scan_state,active_branch,cmd);
315323
elseif (strcmp(cmd,"C")==0)
316324
status=exec_command_C(scan_state,active_branch);
317325
elseif (strcmp(cmd,"c")==0||strcmp(cmd,"connect")==0)
318326
status=exec_command_connect(scan_state,active_branch);
319327
elseif (strcmp(cmd,"cd")==0)
320328
status=exec_command_cd(scan_state,active_branch,cmd);
329+
elseif (strcmp(cmd,"close")==0)
330+
status=exec_command_close(scan_state,active_branch,cmd);
321331
elseif (strcmp(cmd,"conninfo")==0)
322332
status=exec_command_conninfo(scan_state,active_branch);
323333
elseif (pg_strcasecmp(cmd,"copy")==0)
@@ -379,6 +389,8 @@ exec_command(const char *cmd,
379389
elseif (strcmp(cmd,"p")==0||strcmp(cmd,"print")==0)
380390
status=exec_command_print(scan_state,active_branch,
381391
query_buf,previous_buf);
392+
elseif (strcmp(cmd,"parse")==0)
393+
status=exec_command_parse(scan_state,active_branch,cmd);
382394
elseif (strcmp(cmd,"password")==0)
383395
status=exec_command_password(scan_state,active_branch);
384396
elseif (strcmp(cmd,"prompt")==0)
@@ -472,6 +484,7 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
472484
intnalloc=0;
473485

474486
pset.bind_params=NULL;
487+
pset.stmtName=NULL;
475488

476489
while ((opt=psql_scan_slash_option(scan_state,OT_NORMAL,NULL, false)))
477490
{
@@ -485,7 +498,57 @@ exec_command_bind(PsqlScanState scan_state, bool active_branch)
485498
}
486499

487500
pset.bind_nparams=nparams;
488-
pset.bind_flag= true;
501+
pset.send_mode=PSQL_SEND_EXTENDED_QUERY_PARAMS;
502+
}
503+
else
504+
ignore_slash_options(scan_state);
505+
506+
returnstatus;
507+
}
508+
509+
/*
510+
* \bind_named -- set query parameters for an existing prepared statement
511+
*/
512+
staticbackslashResult
513+
exec_command_bind_named(PsqlScanStatescan_state,boolactive_branch,
514+
constchar*cmd)
515+
{
516+
backslashResultstatus=PSQL_CMD_SKIP_LINE;
517+
518+
if (active_branch)
519+
{
520+
char*opt;
521+
intnparams=0;
522+
intnalloc=0;
523+
524+
pset.bind_params=NULL;
525+
pset.stmtName=NULL;
526+
527+
/* get the mandatory prepared statement name */
528+
opt=psql_scan_slash_option(scan_state,OT_NORMAL,NULL, false);
529+
if (!opt)
530+
{
531+
pg_log_error("\\%s: missing required argument",cmd);
532+
status=PSQL_CMD_ERROR;
533+
}
534+
else
535+
{
536+
pset.stmtName=opt;
537+
pset.send_mode=PSQL_SEND_EXTENDED_QUERY_PREPARED;
538+
539+
/* set of parameters */
540+
while ((opt=psql_scan_slash_option(scan_state,OT_NORMAL,NULL, false)))
541+
{
542+
nparams++;
543+
if (nparams>nalloc)
544+
{
545+
nalloc=nalloc ?nalloc*2 :1;
546+
pset.bind_params=pg_realloc_array(pset.bind_params,char*,nalloc);
547+
}
548+
pset.bind_params[nparams-1]=opt;
549+
}
550+
pset.bind_nparams=nparams;
551+
}
489552
}
490553
else
491554
ignore_slash_options(scan_state);
@@ -643,6 +706,38 @@ exec_command_cd(PsqlScanState scan_state, bool active_branch, const char *cmd)
643706
returnsuccess ?PSQL_CMD_SKIP_LINE :PSQL_CMD_ERROR;
644707
}
645708

709+
/*
710+
* \close -- close a previously prepared statement
711+
*/
712+
staticbackslashResult
713+
exec_command_close(PsqlScanStatescan_state,boolactive_branch,constchar*cmd)
714+
{
715+
backslashResultstatus=PSQL_CMD_SKIP_LINE;
716+
717+
if (active_branch)
718+
{
719+
char*opt=psql_scan_slash_option(scan_state,
720+
OT_NORMAL,NULL, false);
721+
722+
pset.stmtName=NULL;
723+
if (!opt)
724+
{
725+
pg_log_error("\\%s: missing required argument",cmd);
726+
status=PSQL_CMD_ERROR;
727+
}
728+
else
729+
{
730+
pset.stmtName=opt;
731+
pset.send_mode=PSQL_SEND_EXTENDED_CLOSE;
732+
status=PSQL_CMD_SEND;
733+
}
734+
}
735+
else
736+
ignore_slash_options(scan_state);
737+
738+
returnstatus;
739+
}
740+
646741
/*
647742
* \conninfo -- display information about the current connection
648743
*/
@@ -2096,6 +2191,39 @@ exec_command_print(PsqlScanState scan_state, bool active_branch,
20962191
returnPSQL_CMD_SKIP_LINE;
20972192
}
20982193

2194+
/*
2195+
* \parse -- parse query
2196+
*/
2197+
staticbackslashResult
2198+
exec_command_parse(PsqlScanStatescan_state,boolactive_branch,
2199+
constchar*cmd)
2200+
{
2201+
backslashResultstatus=PSQL_CMD_SKIP_LINE;
2202+
2203+
if (active_branch)
2204+
{
2205+
char*opt=psql_scan_slash_option(scan_state,
2206+
OT_NORMAL,NULL, false);
2207+
2208+
pset.stmtName=NULL;
2209+
if (!opt)
2210+
{
2211+
pg_log_error("\\%s: missing required argument",cmd);
2212+
status=PSQL_CMD_ERROR;
2213+
}
2214+
else
2215+
{
2216+
pset.stmtName=opt;
2217+
pset.send_mode=PSQL_SEND_EXTENDED_PARSE;
2218+
status=PSQL_CMD_SEND;
2219+
}
2220+
}
2221+
else
2222+
ignore_slash_options(scan_state);
2223+
2224+
returnstatus;
2225+
}
2226+
20992227
/*
21002228
* \password -- set user password
21012229
*/

‎src/bin/psql/common.c

Lines changed: 47 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1274,15 +1274,28 @@ SendQuery(const char *query)
12741274
pset.gsavepopt=NULL;
12751275
}
12761276

1277-
/* clean up after\bind */
1278-
if (pset.bind_flag)
1277+
/* clean up afterextended protocol queries */
1278+
switch (pset.send_mode)
12791279
{
1280-
for (i=0;i<pset.bind_nparams;i++)
1281-
free(pset.bind_params[i]);
1282-
free(pset.bind_params);
1283-
pset.bind_params=NULL;
1284-
pset.bind_flag= false;
1280+
casePSQL_SEND_EXTENDED_CLOSE:/* \close */
1281+
free(pset.stmtName);
1282+
break;
1283+
casePSQL_SEND_EXTENDED_PARSE:/* \parse */
1284+
free(pset.stmtName);
1285+
break;
1286+
casePSQL_SEND_EXTENDED_QUERY_PARAMS:/* \bind */
1287+
casePSQL_SEND_EXTENDED_QUERY_PREPARED:/* \bind_named */
1288+
for (i=0;i<pset.bind_nparams;i++)
1289+
free(pset.bind_params[i]);
1290+
free(pset.bind_params);
1291+
free(pset.stmtName);
1292+
pset.bind_params=NULL;
1293+
break;
1294+
casePSQL_SEND_QUERY:
1295+
break;
12851296
}
1297+
pset.stmtName=NULL;
1298+
pset.send_mode=PSQL_SEND_QUERY;
12861299

12871300
/* reset \gset trigger */
12881301
if (pset.gset_prefix)
@@ -1456,7 +1469,7 @@ ExecQueryAndProcessResults(const char *query,
14561469
constprintQueryOpt*opt,FILE*printQueryFout)
14571470
{
14581471
booltiming=pset.timing;
1459-
boolsuccess;
1472+
boolsuccess= false;
14601473
boolreturn_early= false;
14611474
instr_timebefore,
14621475
after;
@@ -1469,10 +1482,32 @@ ExecQueryAndProcessResults(const char *query,
14691482
else
14701483
INSTR_TIME_SET_ZERO(before);
14711484

1472-
if (pset.bind_flag)
1473-
success=PQsendQueryParams(pset.db,query,pset.bind_nparams,NULL, (constchar*const*)pset.bind_params,NULL,NULL,0);
1474-
else
1475-
success=PQsendQuery(pset.db,query);
1485+
switch (pset.send_mode)
1486+
{
1487+
casePSQL_SEND_EXTENDED_CLOSE:
1488+
success=PQsendClosePrepared(pset.db,pset.stmtName);
1489+
break;
1490+
casePSQL_SEND_EXTENDED_PARSE:
1491+
success=PQsendPrepare(pset.db,pset.stmtName,query,0,NULL);
1492+
break;
1493+
casePSQL_SEND_EXTENDED_QUERY_PARAMS:
1494+
Assert(pset.stmtName==NULL);
1495+
success=PQsendQueryParams(pset.db,query,
1496+
pset.bind_nparams,NULL,
1497+
(constchar*const*)pset.bind_params,
1498+
NULL,NULL,0);
1499+
break;
1500+
casePSQL_SEND_EXTENDED_QUERY_PREPARED:
1501+
Assert(pset.stmtName!=NULL);
1502+
success=PQsendQueryPrepared(pset.db,pset.stmtName,
1503+
pset.bind_nparams,
1504+
(constchar*const*)pset.bind_params,
1505+
NULL,NULL,0);
1506+
break;
1507+
casePSQL_SEND_QUERY:
1508+
success=PQsendQuery(pset.db,query);
1509+
break;
1510+
}
14761511

14771512
if (!success)
14781513
{

0 commit comments

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