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

Commitd2d153f

Browse files
committed
Create a psql command \gset to store query results into psql variables.
This eases manipulation of query results in psql scripts.Pavel Stehule, reviewed by Piyush Newe, Shigeru Hanada, and Tom Lane
1 parent101d6ae commitd2d153f

File tree

10 files changed

+254
-15
lines changed

10 files changed

+254
-15
lines changed

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

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1620,6 +1620,46 @@ Tue Oct 26 21:40:57 CEST 1999
16201620
</listitem>
16211621
</varlistentry>
16221622

1623+
<varlistentry>
1624+
<term><literal>\gset</literal> [ <replaceable class="parameter">prefix</replaceable> ]</term>
1625+
1626+
<listitem>
1627+
<para>
1628+
Sends the current query input buffer to the server and stores the
1629+
query's output into <application>psql</> variables (see <xref
1630+
linkend="APP-PSQL-variables" endterm="APP-PSQL-variables-title">).
1631+
The query to be executed must return exactly one row. Each column of
1632+
the row is stored into a separate variable, named the same as the
1633+
column. For example:
1634+
<programlisting>
1635+
=&gt; <userinput>SELECT 'hello' AS var1, 10 AS var2</userinput>
1636+
-&gt; <userinput>\gset</userinput>
1637+
=&gt; <userinput>\echo :var1 :var2</userinput>
1638+
hello 10
1639+
</programlisting>
1640+
</para>
1641+
<para>
1642+
If you specify a <replaceable class="parameter">prefix</replaceable>,
1643+
that string is prepended to the query's column names to create the
1644+
variable names to use:
1645+
<programlisting>
1646+
=&gt; <userinput>SELECT 'hello' AS var1, 10 AS var2</userinput>
1647+
-&gt; <userinput>\gset result_</userinput>
1648+
=&gt; <userinput>\echo :result_var1 :result_var2</userinput>
1649+
hello 10
1650+
</programlisting>
1651+
</para>
1652+
<para>
1653+
If a column result is NULL, the corresponding variable is unset
1654+
rather than being set.
1655+
</para>
1656+
<para>
1657+
If the query fails or does not return one row,
1658+
no variables are changed.
1659+
</para>
1660+
</listitem>
1661+
</varlistentry>
1662+
16231663
<varlistentry>
16241664
<term><literal>\h</literal> or <literal>\help</literal> <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term>
16251665
<listitem>

‎src/bin/psql/command.c

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -731,7 +731,7 @@ exec_command(const char *cmd,
731731
free(fname);
732732
}
733733

734-
/* \g [filename]means send query, optionally with output to file/pipe */
734+
/* \g [filename]-- send query, optionally with output to file/pipe */
735735
elseif (strcmp(cmd,"g")==0)
736736
{
737737
char*fname=psql_scan_slash_option(scan_state,
@@ -748,6 +748,22 @@ exec_command(const char *cmd,
748748
status=PSQL_CMD_SEND;
749749
}
750750

751+
/* \gset [prefix] -- send query and store result into variables */
752+
elseif (strcmp(cmd,"gset")==0)
753+
{
754+
char*prefix=psql_scan_slash_option(scan_state,
755+
OT_NORMAL,NULL, false);
756+
757+
if (prefix)
758+
pset.gset_prefix=prefix;
759+
else
760+
{
761+
/* we must set a non-NULL prefix to trigger storing */
762+
pset.gset_prefix=pg_strdup("");
763+
}
764+
status=PSQL_CMD_SEND;
765+
}
766+
751767
/* help */
752768
elseif (strcmp(cmd,"h")==0||strcmp(cmd,"help")==0)
753769
{

‎src/bin/psql/common.c

Lines changed: 96 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -43,7 +43,7 @@ pg_strdup(const char *string)
4343
if (!string)
4444
{
4545
psql_error("%s: pg_strdup: cannot duplicate null pointer (internal error)\n",
46-
pset.progname);
46+
pset.progname);
4747
exit(EXIT_FAILURE);
4848
}
4949
tmp=strdup(string);
@@ -615,6 +615,65 @@ PrintQueryTuples(const PGresult *results)
615615
}
616616

617617

618+
/*
619+
* StoreQueryTuple: assuming query result is OK, save data into variables
620+
*
621+
* Returns true if successful, false otherwise.
622+
*/
623+
staticbool
624+
StoreQueryTuple(constPGresult*result)
625+
{
626+
boolsuccess= true;
627+
628+
if (PQntuples(result)<1)
629+
{
630+
psql_error("no rows returned for \\gset\n");
631+
success= false;
632+
}
633+
elseif (PQntuples(result)>1)
634+
{
635+
psql_error("more than one row returned for \\gset\n");
636+
success= false;
637+
}
638+
else
639+
{
640+
inti;
641+
642+
for (i=0;i<PQnfields(result);i++)
643+
{
644+
char*colname=PQfname(result,i);
645+
char*varname;
646+
char*value;
647+
648+
/* concate prefix and column name */
649+
varname=pg_malloc(strlen(pset.gset_prefix)+strlen(colname)+1);
650+
strcpy(varname,pset.gset_prefix);
651+
strcat(varname,colname);
652+
653+
if (!PQgetisnull(result,0,i))
654+
value=PQgetvalue(result,0,i);
655+
else
656+
{
657+
/* for NULL value, unset rather than set the variable */
658+
value=NULL;
659+
}
660+
661+
if (!SetVariable(pset.vars,varname,value))
662+
{
663+
psql_error("could not set variable \"%s\"\n",varname);
664+
free(varname);
665+
success= false;
666+
break;
667+
}
668+
669+
free(varname);
670+
}
671+
}
672+
673+
returnsuccess;
674+
}
675+
676+
618677
/*
619678
* ProcessResult: utility function for use by SendQuery() only
620679
*
@@ -752,7 +811,7 @@ PrintQueryStatus(PGresult *results)
752811

753812

754813
/*
755-
* PrintQueryResults: print out query results as required
814+
* PrintQueryResults: print out(or store)query results as required
756815
*
757816
* Note: Utility function for use by SendQuery() only.
758817
*
@@ -770,8 +829,11 @@ PrintQueryResults(PGresult *results)
770829
switch (PQresultStatus(results))
771830
{
772831
casePGRES_TUPLES_OK:
773-
/* print the data ... */
774-
success=PrintQueryTuples(results);
832+
/* store or print the data ... */
833+
if (pset.gset_prefix)
834+
success=StoreQueryTuple(results);
835+
else
836+
success=PrintQueryTuples(results);
775837
/* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
776838
cmdstatus=PQcmdStatus(results);
777839
if (strncmp(cmdstatus,"INSERT",6)==0||
@@ -898,7 +960,7 @@ SendQuery(const char *query)
898960
if (on_error_rollback_warning== false&&pset.sversion<80000)
899961
{
900962
psql_error("The server (version %d.%d) does not support savepoints for ON_ERROR_ROLLBACK.\n",
901-
pset.sversion /10000, (pset.sversion /100) %100);
963+
pset.sversion /10000, (pset.sversion /100) %100);
902964
on_error_rollback_warning= true;
903965
}
904966
else
@@ -1046,6 +1108,13 @@ SendQuery(const char *query)
10461108
pset.gfname=NULL;
10471109
}
10481110

1111+
/* reset \gset trigger */
1112+
if (pset.gset_prefix)
1113+
{
1114+
free(pset.gset_prefix);
1115+
pset.gset_prefix=NULL;
1116+
}
1117+
10491118
returnOK;
10501119
}
10511120

@@ -1072,6 +1141,7 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
10721141
boolstarted_txn= false;
10731142
booldid_pager= false;
10741143
intntuples;
1144+
intfetch_count;
10751145
charfetch_cmd[64];
10761146
instr_timebefore,
10771147
after;
@@ -1119,9 +1189,18 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
11191189
*elapsed_msec+=INSTR_TIME_GET_MILLISEC(after);
11201190
}
11211191

1192+
/*
1193+
* In \gset mode, we force the fetch count to be 2, so that we will throw
1194+
* the appropriate error if the query returns more than one row.
1195+
*/
1196+
if (pset.gset_prefix)
1197+
fetch_count=2;
1198+
else
1199+
fetch_count=pset.fetch_count;
1200+
11221201
snprintf(fetch_cmd,sizeof(fetch_cmd),
11231202
"FETCH FORWARD %d FROM _psql_cursor",
1124-
pset.fetch_count);
1203+
fetch_count);
11251204

11261205
/* prepare to write output to \g argument, if any */
11271206
if (pset.gfname)
@@ -1147,7 +1226,7 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
11471226
if (pset.timing)
11481227
INSTR_TIME_SET_CURRENT(before);
11491228

1150-
/* getFETCH_COUNT tuples at a time */
1229+
/* getfetch_count tuples at a time */
11511230
results=PQexec(pset.db,fetch_cmd);
11521231

11531232
if (pset.timing)
@@ -1174,9 +1253,17 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
11741253
break;
11751254
}
11761255

1256+
if (pset.gset_prefix)
1257+
{
1258+
/* StoreQueryTuple will complain if not exactly one row */
1259+
OK=StoreQueryTuple(results);
1260+
PQclear(results);
1261+
break;
1262+
}
1263+
11771264
ntuples=PQntuples(results);
11781265

1179-
if (ntuples<pset.fetch_count)
1266+
if (ntuples<fetch_count)
11801267
{
11811268
/* this is the last result set, so allow footer decoration */
11821269
my_popt.topt.stop_table= true;
@@ -1214,7 +1301,7 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
12141301
* writing things to the stream, we presume $PAGER has disappeared and
12151302
* stop bothering to pull down more data.
12161303
*/
1217-
if (ntuples<pset.fetch_count||cancel_pressed||flush_error||
1304+
if (ntuples<fetch_count||cancel_pressed||flush_error||
12181305
ferror(pset.queryFout))
12191306
break;
12201307
}

‎src/bin/psql/help.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -165,13 +165,14 @@ slashUsage(unsigned short int pager)
165165

166166
currdb=PQdb(pset.db);
167167

168-
output=PageOutput(94,pager);
168+
output=PageOutput(95,pager);
169169

170170
/* if you add/remove a line here, change the row count above */
171171

172172
fprintf(output,_("General\n"));
173173
fprintf(output,_(" \\copyright show PostgreSQL usage and distribution terms\n"));
174174
fprintf(output,_(" \\g [FILE] or ; execute query (and send results to file or |pipe)\n"));
175+
fprintf(output,_(" \\gset [PREFIX] execute query and store results in psql variables\n"));
175176
fprintf(output,_(" \\h [NAME] help on syntax of SQL commands, * for all commands\n"));
176177
fprintf(output,_(" \\q quit psql\n"));
177178
fprintf(output,"\n");
@@ -261,7 +262,7 @@ slashUsage(unsigned short int pager)
261262
currdb);
262263
else
263264
fprintf(output,_(" \\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]\n"
264-
" connect to new database (currently no connection)\n")),
265+
" connect to new database (currently no connection)\n"));
265266
fprintf(output,_(" \\encoding [ENCODING] show or set client encoding\n"));
266267
fprintf(output,_(" \\password [USERNAME] securely change the password for a user\n"));
267268
fprintf(output,_(" \\conninfo display information about current connection\n"));

‎src/bin/psql/settings.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -73,6 +73,7 @@ typedef struct _psqlSettings
7373
printQueryOptpopt;
7474

7575
char*gfname;/* one-shot file output argument for \g */
76+
char*gset_prefix;/* one-shot prefix argument for \gset */
7677

7778
boolnotty;/* stdin or stdout is not a tty (as determined
7879
* on startup) */

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -856,7 +856,7 @@ psql_completion(char *text, int start, int end)
856856
"\\dF","\\dFd","\\dFp","\\dFt","\\dg","\\di","\\dl","\\dL",
857857
"\\dn","\\do","\\dp","\\drds","\\ds","\\dS","\\dt","\\dT","\\dv","\\du",
858858
"\\e","\\echo","\\ef","\\encoding",
859-
"\\f","\\g","\\h","\\help","\\H","\\i","\\ir","\\l",
859+
"\\f","\\g","\\gset","\\h","\\help","\\H","\\i","\\ir","\\l",
860860
"\\lo_import","\\lo_export","\\lo_list","\\lo_unlink",
861861
"\\o","\\p","\\password","\\prompt","\\pset","\\q","\\qecho","\\r",
862862
"\\set","\\sf","\\t","\\T",

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

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,54 @@
1+
--
2+
-- Tests for psql features that aren't closely connected to any
3+
-- specific server features
4+
--
5+
-- \gset
6+
select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
7+
\echo :pref01_test01 :pref01_test02 :pref01_test03
8+
10 20 Hello
9+
-- should fail: bad variable name
10+
select 10 as "bad name"
11+
\gset
12+
could not set variable "bad name"
13+
-- multiple backslash commands in one line
14+
select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x
15+
1
16+
select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y
17+
3
18+
4
19+
select 5 as x, 6 as y \gset pref01_ \\ \g \echo :pref01_x :pref01_y
20+
x | y
21+
---+---
22+
5 | 6
23+
(1 row)
24+
25+
5 6
26+
select 7 as x, 8 as y \g \gset pref01_ \echo :pref01_x :pref01_y
27+
x | y
28+
---+---
29+
7 | 8
30+
(1 row)
31+
32+
7 8
33+
-- NULL should unset the variable
34+
\set var2 xyz
35+
select 1 as var1, NULL as var2, 3 as var3 \gset
36+
\echo :var1 :var2 :var3
37+
1 :var2 3
38+
-- \gset requires just one tuple
39+
select 10 as test01, 20 as test02 from generate_series(1,3) \gset
40+
more than one row returned for \gset
41+
select 10 as test01, 20 as test02 from generate_series(1,0) \gset
42+
no rows returned for \gset
43+
-- \gset should work in FETCH_COUNT mode too
44+
\set FETCH_COUNT 1
45+
select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x
46+
1
47+
select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y
48+
3
49+
4
50+
select 10 as test01, 20 as test02 from generate_series(1,3) \gset
51+
more than one row returned for \gset
52+
select 10 as test01, 20 as test02 from generate_series(1,0) \gset
53+
no rows returned for \gset
54+
\unset FETCH_COUNT

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -88,7 +88,7 @@ test: privileges security_label collate
8888
# ----------
8989
# Another group of parallel tests
9090
# ----------
91-
test: miscalter_generic
91+
test:alter_genericmiscpsql
9292

9393
# rules cannot run concurrently with any test that creates a view
9494
test: rules

‎src/test/regress/serial_schedule

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -95,8 +95,9 @@ test: prepared_xacts
9595
test: privileges
9696
test: security_label
9797
test: collate
98-
test: misc
9998
test: alter_generic
99+
test: misc
100+
test: psql
100101
test: rules
101102
test: event_trigger
102103
test: select_views

0 commit comments

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