Skip to content

Commit 2bbe911

Browse files
tglsfdctglsfdc
committed
Add a \gexec command to psql for evaluation of computed queries.
\gexec executes the just-entered query, like \g, but instead of printing the results it takes each field as a SQL command to send to the server. Computing a series of queries to be executed is a fairly common thing, but up to now you always had to resort to kluges like writing the queries to a file and then inputting the file. Now it can be done with no intermediate step. The implementation is fairly straightforward except for its interaction with FETCH_COUNT. ExecQueryUsingCursor isn't capable of being called recursively, and even if it were, its need to create a transaction block interferes unpleasantly with the desired behavior of \gexec after a failure of a generated query (i.e., that it can continue). Therefore, disable use of ExecQueryUsingCursor when doing the master \gexec query. We can still apply it to individual generated queries, however, and there might be some value in doing so. While testing this feature's interaction with single-step mode, I (tgl) was led to conclude that SendQuery needs to recognize SIGINT (cancel_pressed) as a negative response to the single-step prompt. Perhaps that's a back-patchable bug fix, but for now I just included it here. Corey Huinker, reviewed by Jim Nasby, Daniel Vérité, and myself
1 parent 66229ac commit 2bbe911

File tree

8 files changed

+207
-6
lines changed

8 files changed

+207
-6
lines changed

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

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1766,6 +1766,49 @@ Tue Oct 26 21:40:57 CEST 1999
17661766
</listitem>
17671767
</varlistentry>
17681768

1769+
1770+
<varlistentry>
1771+
<term><literal>\gexec</literal></term>
1772+
1773+
<listitem>
1774+
<para>
1775+
Sends the current query input buffer to the server, then treats
1776+
each column of each row of the query's output (if any) as a SQL
1777+
statement to be executed. For example, to create an index on each
1778+
column of <structname>my_table</>:
1779+
<programlisting>
1780+
=&gt; <userinput>SELECT format('create index on my_table(%I)', attname)</>
1781+
-&gt; <userinput>FROM pg_attribute</>
1782+
-&gt; <userinput>WHERE attrelid = 'my_table'::regclass AND attnum &gt; 0</>
1783+
-&gt; <userinput>ORDER BY attnum</>
1784+
-&gt; <userinput>\gexec</>
1785+
CREATE INDEX
1786+
CREATE INDEX
1787+
CREATE INDEX
1788+
CREATE INDEX
1789+
</programlisting>
1790+
</para>
1791+
1792+
<para>
1793+
The generated queries are executed in the order in which the rows
1794+
are returned, and left-to-right within each row if there is more
1795+
than one column. NULL fields are ignored. The generated queries
1796+
are sent literally to the server for processing, so they cannot be
1797+
<application>psql</> meta-commands nor contain <application>psql</>
1798+
variable references. If any individual query fails, execution of
1799+
the remaining queries continues
1800+
unless <varname>ON_ERROR_STOP</varname> is set. Execution of each
1801+
query is subject to <varname>ECHO</varname> processing.
1802+
(Setting <varname>ECHO</varname> to <literal>all</literal>
1803+
or <literal>queries</literal> is often advisable when
1804+
using <command>\gexec</>.) Query logging, single-step mode,
1805+
timing, and other query execution features apply to each generated
1806+
query as well.
1807+
</para>
1808+
</listitem>
1809+
</varlistentry>
1810+
1811+
17691812
<varlistentry>
17701813
<term><literal>\gset [ <replaceable class="parameter">prefix</replaceable> ]</literal></term>
17711814

‎src/bin/psql/command.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -871,6 +871,13 @@ exec_command(const char *cmd,
871871
status = PSQL_CMD_SEND;
872872
}
873873

874+
/* \gexec -- send query and execute each field of result */
875+
else if (strcmp(cmd, "gexec") == 0)
876+
{
877+
pset.gexec_flag = true;
878+
status = PSQL_CMD_SEND;
879+
}
880+
874881
/* \gset [prefix] -- send query and store result into variables */
875882
else if (strcmp(cmd, "gset") == 0)
876883
{

‎src/bin/psql/common.c

Lines changed: 86 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -797,6 +797,76 @@ StoreQueryTuple(const PGresult *result)
797797
}
798798

799799

800+
/*
801+
* ExecQueryTuples: assuming query result is OK, execute each query
802+
* result field as a SQL statement
803+
*
804+
* Returns true if successful, false otherwise.
805+
*/
806+
static bool
807+
ExecQueryTuples(const PGresult *result)
808+
{
809+
bool success = true;
810+
int nrows = PQntuples(result);
811+
int ncolumns = PQnfields(result);
812+
int r,
813+
c;
814+
815+
/*
816+
* We must turn off gexec_flag to avoid infinite recursion. Note that
817+
* this allows ExecQueryUsingCursor to be applied to the individual query
818+
* results. SendQuery prevents it from being applied when fetching the
819+
* queries-to-execute, because it can't handle recursion either.
820+
*/
821+
pset.gexec_flag = false;
822+
823+
for (r = 0; r < nrows; r++)
824+
{
825+
for (c = 0; c < ncolumns; c++)
826+
{
827+
if (!PQgetisnull(result, r, c))
828+
{
829+
const char *query = PQgetvalue(result, r, c);
830+
831+
/* Abandon execution if cancel_pressed */
832+
if (cancel_pressed)
833+
goto loop_exit;
834+
835+
/*
836+
* ECHO_ALL mode should echo these queries, but SendQuery
837+
* assumes that MainLoop did that, so we have to do it here.
838+
*/
839+
if (pset.echo == PSQL_ECHO_ALL && !pset.singlestep)
840+
{
841+
puts(query);
842+
fflush(stdout);
843+
}
844+
845+
if (!SendQuery(query))
846+
{
847+
/* Error - abandon execution if ON_ERROR_STOP */
848+
success = false;
849+
if (pset.on_error_stop)
850+
goto loop_exit;
851+
}
852+
}
853+
}
854+
}
855+
856+
loop_exit:
857+
858+
/*
859+
* Restore state. We know gexec_flag was on, else we'd not be here. (We
860+
* also know it'll get turned off at end of command, but that's not ours
861+
* to do here.)
862+
*/
863+
pset.gexec_flag = true;
864+
865+
/* Return true if all queries were successful */
866+
return success;
867+
}
868+
869+
800870
/*
801871
* ProcessResult: utility function for use by SendQuery() only
802872
*
@@ -971,7 +1041,7 @@ PrintQueryStatus(PGresult *results)
9711041

9721042

9731043
/*
974-
* PrintQueryResults: print out (or store) query results as required
1044+
* PrintQueryResults: print out (or store or execute) query results as required
9751045
*
9761046
* Note: Utility function for use by SendQuery() only.
9771047
*
@@ -989,9 +1059,11 @@ PrintQueryResults(PGresult *results)
9891059
switch (PQresultStatus(results))
9901060
{
9911061
case PGRES_TUPLES_OK:
992-
/* store or print the data ... */
1062+
/* store or execute or print the data ... */
9931063
if (pset.gset_prefix)
9941064
success = StoreQueryTuple(results);
1065+
else if (pset.gexec_flag)
1066+
success = ExecQueryTuples(results);
9951067
else
9961068
success = PrintQueryTuples(results);
9971069
/* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
@@ -1068,6 +1140,7 @@ SendQuery(const char *query)
10681140
{
10691141
char buf[3];
10701142

1143+
fflush(stderr);
10711144
printf(_("***(Single step mode: verify command)*******************************************\n"
10721145
"%s\n"
10731146
"***(press return to proceed or enter x and return to cancel)********************\n"),
@@ -1076,6 +1149,8 @@ SendQuery(const char *query)
10761149
if (fgets(buf, sizeof(buf), stdin) != NULL)
10771150
if (buf[0] == 'x')
10781151
goto sendquery_cleanup;
1152+
if (cancel_pressed)
1153+
goto sendquery_cleanup;
10791154
}
10801155
else if (pset.echo == PSQL_ECHO_QUERIES)
10811156
{
@@ -1138,7 +1213,7 @@ SendQuery(const char *query)
11381213
}
11391214
}
11401215

1141-
if (pset.fetch_count <= 0 || !is_select_command(query))
1216+
if (pset.fetch_count <= 0 || pset.gexec_flag || !is_select_command(query))
11421217
{
11431218
/* Default fetch-it-all-and-print mode */
11441219
instr_time before,
@@ -1278,6 +1353,9 @@ SendQuery(const char *query)
12781353
pset.gset_prefix = NULL;
12791354
}
12801355

1356+
/* reset \gexec trigger */
1357+
pset.gexec_flag = false;
1358+
12811359
return OK;
12821360
}
12831361

@@ -1423,6 +1501,8 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
14231501
break;
14241502
}
14251503

1504+
/* Note we do not deal with \gexec mode here */
1505+
14261506
ntuples = PQntuples(results);
14271507

14281508
if (ntuples < fetch_count)
@@ -1499,8 +1579,10 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
14991579
{
15001580
OK = AcceptResult(results) &&
15011581
(PQresultStatus(results) == PGRES_COMMAND_OK);
1582+
ClearOrSaveResult(results);
15021583
}
1503-
ClearOrSaveResult(results);
1584+
else
1585+
PQclear(results);
15041586

15051587
if (started_txn)
15061588
{

‎src/bin/psql/help.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -168,12 +168,13 @@ slashUsage(unsigned short int pager)
168168
* Use "psql --help=commands | wc" to count correctly. It's okay to count
169169
* the USE_READLINE line even in builds without that.
170170
*/
171-
output = PageOutput(110, pager ? &(pset.popt.topt) : NULL);
171+
output = PageOutput(111, pager ? &(pset.popt.topt) : NULL);
172172

173173
fprintf(output, _("General\n"));
174174
fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n"));
175175
fprintf(output, _(" \\errverbose show most recent error message at maximum verbosity\n"));
176176
fprintf(output, _(" \\g [FILE] or ; execute query (and send results to file or |pipe)\n"));
177+
fprintf(output, _(" \\gexec execute query, then execute each value in its result\n"));
177178
fprintf(output, _(" \\gset [PREFIX] execute query and store results in psql variables\n"));
178179
fprintf(output, _(" \\q quit psql\n"));
179180
fprintf(output, _(" \\watch [SEC] execute query every SEC seconds\n"));

‎src/bin/psql/settings.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -92,6 +92,7 @@ typedef struct _psqlSettings
9292

9393
char *gfname; /* one-shot file output argument for \g */
9494
char *gset_prefix; /* one-shot prefix argument for \gset */
95+
bool gexec_flag; /* one-shot flag to execute query's results */
9596

9697
bool notty; /* stdin or stdout is not a tty (as determined
9798
* on startup) */

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1281,7 +1281,7 @@ psql_completion(const char *text, int start, int end)
12811281
"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\drds", "\\ds", "\\dS",
12821282
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
12831283
"\\e", "\\echo", "\\ef", "\\encoding", "\\errverbose", "\\ev",
1284-
"\\f", "\\g", "\\gset", "\\h", "\\help", "\\H", "\\i", "\\ir", "\\l",
1284+
"\\f", "\\g", "\\gexec", "\\gset", "\\h", "\\help", "\\H", "\\i", "\\ir", "\\l",
12851285
"\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
12861286
"\\o", "\\p", "\\password", "\\prompt", "\\pset", "\\q", "\\qecho", "\\r",
12871287
"\\s", "\\set", "\\setenv", "\\sf", "\\sv", "\\t", "\\T",

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

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -51,6 +51,51 @@ select 10 as test01, 20 as test02 from generate_series(1,3) \gset
5151
more than one row returned for \gset
5252
select 10 as test01, 20 as test02 from generate_series(1,0) \gset
5353
no rows returned for \gset
54+
\unset FETCH_COUNT
55+
-- \gexec
56+
create temporary table gexec_test(a int, b text, c date, d float);
57+
select format('create index on gexec_test(%I)', attname)
58+
from pg_attribute
59+
where attrelid = 'gexec_test'::regclass and attnum > 0
60+
order by attnum
61+
\gexec
62+
create index on gexec_test(a)
63+
create index on gexec_test(b)
64+
create index on gexec_test(c)
65+
create index on gexec_test(d)
66+
-- \gexec should work in FETCH_COUNT mode too
67+
-- (though the fetch limit applies to the executed queries not the meta query)
68+
\set FETCH_COUNT 1
69+
select 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)'
70+
union all
71+
select 'drop table gexec_test', NULL
72+
union all
73+
select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over'
74+
\gexec
75+
select 1 as ones
76+
ones
77+
------
78+
1
79+
(1 row)
80+
81+
select x.y, x.y*2 as double from generate_series(1,4) as x(y)
82+
y | double
83+
---+--------
84+
1 | 2
85+
2 | 4
86+
3 | 6
87+
4 | 8
88+
(4 rows)
89+
90+
drop table gexec_test
91+
drop table gexec_test
92+
ERROR: table "gexec_test" does not exist
93+
select '2000-01-01'::date as party_over
94+
party_over
95+
------------
96+
01-01-2000
97+
(1 row)
98+
5499
\unset FETCH_COUNT
55100
-- show all pset options
56101
\pset

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,28 @@ select 10 as test01, 20 as test02 from generate_series(1,0) \gset
3838

3939
\unset FETCH_COUNT
4040

41+
-- \gexec
42+
43+
create temporary table gexec_test(a int, b text, c date, d float);
44+
select format('create index on gexec_test(%I)', attname)
45+
from pg_attribute
46+
where attrelid = 'gexec_test'::regclass and attnum > 0
47+
order by attnum
48+
\gexec
49+
50+
-- \gexec should work in FETCH_COUNT mode too
51+
-- (though the fetch limit applies to the executed queries not the meta query)
52+
\set FETCH_COUNT 1
53+
54+
select 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)'
55+
union all
56+
select 'drop table gexec_test', NULL
57+
union all
58+
select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over'
59+
\gexec
60+
61+
\unset FETCH_COUNT
62+
4163
-- show all pset options
4264
\pset
4365

0 commit comments

Comments
 (0)