diff options
| -rw-r--r-- | doc/src/sgml/ref/psql-ref.sgml | 43 | ||||
| -rw-r--r-- | src/bin/psql/command.c | 7 | ||||
| -rw-r--r-- | src/bin/psql/common.c | 90 | ||||
| -rw-r--r-- | src/bin/psql/help.c | 3 | ||||
| -rw-r--r-- | src/bin/psql/settings.h | 1 | ||||
| -rw-r--r-- | src/bin/psql/tab-complete.c | 2 | ||||
| -rw-r--r-- | src/test/regress/expected/psql.out | 45 | ||||
| -rw-r--r-- | src/test/regress/sql/psql.sql | 22 |
8 files changed, 207 insertions, 6 deletions
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index e8afc247afe..d8b9a03ee0e 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1766,6 +1766,49 @@ Tue Oct 26 21:40:57 CEST 1999 </listitem> </varlistentry> + + <varlistentry> + <term><literal>\gexec</literal></term> + + <listitem> + <para> + Sends the current query input buffer to the server, then treats + each column of each row of the query's output (if any) as a SQL + statement to be executed. For example, to create an index on each + column of <structname>my_table</>: +<programlisting> +=> <userinput>SELECT format('create index on my_table(%I)', attname)</> +-> <userinput>FROM pg_attribute</> +-> <userinput>WHERE attrelid = 'my_table'::regclass AND attnum > 0</> +-> <userinput>ORDER BY attnum</> +-> <userinput>\gexec</> +CREATE INDEX +CREATE INDEX +CREATE INDEX +CREATE INDEX +</programlisting> + </para> + + <para> + The generated queries are executed in the order in which the rows + are returned, and left-to-right within each row if there is more + than one column. NULL fields are ignored. The generated queries + are sent literally to the server for processing, so they cannot be + <application>psql</> meta-commands nor contain <application>psql</> + variable references. If any individual query fails, execution of + the remaining queries continues + unless <varname>ON_ERROR_STOP</varname> is set. Execution of each + query is subject to <varname>ECHO</varname> processing. + (Setting <varname>ECHO</varname> to <literal>all</literal> + or <literal>queries</literal> is often advisable when + using <command>\gexec</>.) Query logging, single-step mode, + timing, and other query execution features apply to each generated + query as well. + </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 3401b5183b0..1d326a81afe 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -871,6 +871,13 @@ exec_command(const char *cmd, status = PSQL_CMD_SEND; } + /* \gexec -- send query and execute each field of result */ + else if (strcmp(cmd, "gexec") == 0) + { + pset.gexec_flag = true; + status = PSQL_CMD_SEND; + } + /* \gset [prefix] -- send query and store result into variables */ else if (strcmp(cmd, "gset") == 0) { diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c index a2a07fb538e..df3441cc750 100644 --- a/src/bin/psql/common.c +++ b/src/bin/psql/common.c @@ -798,6 +798,76 @@ StoreQueryTuple(const PGresult *result) /* + * ExecQueryTuples: assuming query result is OK, execute each query + * result field as a SQL statement + * + * Returns true if successful, false otherwise. + */ +static bool +ExecQueryTuples(const PGresult *result) +{ + bool success = true; + int nrows = PQntuples(result); + int ncolumns = PQnfields(result); + int r, + c; + + /* + * We must turn off gexec_flag to avoid infinite recursion. Note that + * this allows ExecQueryUsingCursor to be applied to the individual query + * results. SendQuery prevents it from being applied when fetching the + * queries-to-execute, because it can't handle recursion either. + */ + pset.gexec_flag = false; + + for (r = 0; r < nrows; r++) + { + for (c = 0; c < ncolumns; c++) + { + if (!PQgetisnull(result, r, c)) + { + const char *query = PQgetvalue(result, r, c); + + /* Abandon execution if cancel_pressed */ + if (cancel_pressed) + goto loop_exit; + + /* + * ECHO_ALL mode should echo these queries, but SendQuery + * assumes that MainLoop did that, so we have to do it here. + */ + if (pset.echo == PSQL_ECHO_ALL && !pset.singlestep) + { + puts(query); + fflush(stdout); + } + + if (!SendQuery(query)) + { + /* Error - abandon execution if ON_ERROR_STOP */ + success = false; + if (pset.on_error_stop) + goto loop_exit; + } + } + } + } + +loop_exit: + + /* + * Restore state. We know gexec_flag was on, else we'd not be here. (We + * also know it'll get turned off at end of command, but that's not ours + * to do here.) + */ + pset.gexec_flag = true; + + /* Return true if all queries were successful */ + return success; +} + + +/* * ProcessResult: utility function for use by SendQuery() only * * When our command string contained a COPY FROM STDIN or COPY TO STDOUT, @@ -971,7 +1041,7 @@ PrintQueryStatus(PGresult *results) /* - * PrintQueryResults: print out (or store) query results as required + * PrintQueryResults: print out (or store or execute) query results as required * * Note: Utility function for use by SendQuery() only. * @@ -989,9 +1059,11 @@ PrintQueryResults(PGresult *results) switch (PQresultStatus(results)) { case PGRES_TUPLES_OK: - /* store or print the data ... */ + /* store or execute or print the data ... */ if (pset.gset_prefix) success = StoreQueryTuple(results); + else if (pset.gexec_flag) + success = ExecQueryTuples(results); else success = PrintQueryTuples(results); /* if it's INSERT/UPDATE/DELETE RETURNING, also print status */ @@ -1068,6 +1140,7 @@ SendQuery(const char *query) { char buf[3]; + fflush(stderr); printf(_("***(Single step mode: verify command)*******************************************\n" "%s\n" "***(press return to proceed or enter x and return to cancel)********************\n"), @@ -1076,6 +1149,8 @@ SendQuery(const char *query) if (fgets(buf, sizeof(buf), stdin) != NULL) if (buf[0] == 'x') goto sendquery_cleanup; + if (cancel_pressed) + goto sendquery_cleanup; } else if (pset.echo == PSQL_ECHO_QUERIES) { @@ -1138,7 +1213,7 @@ SendQuery(const char *query) } } - if (pset.fetch_count <= 0 || !is_select_command(query)) + if (pset.fetch_count <= 0 || pset.gexec_flag || !is_select_command(query)) { /* Default fetch-it-all-and-print mode */ instr_time before, @@ -1278,6 +1353,9 @@ sendquery_cleanup: pset.gset_prefix = NULL; } + /* reset \gexec trigger */ + pset.gexec_flag = false; + return OK; } @@ -1423,6 +1501,8 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec) break; } + /* Note we do not deal with \gexec mode here */ + ntuples = PQntuples(results); if (ntuples < fetch_count) @@ -1499,8 +1579,10 @@ cleanup: { OK = AcceptResult(results) && (PQresultStatus(results) == PGRES_COMMAND_OK); + ClearOrSaveResult(results); } - ClearOrSaveResult(results); + else + PQclear(results); if (started_txn) { diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index c6f0993018f..7549451d216 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -168,12 +168,13 @@ slashUsage(unsigned short int pager) * Use "psql --help=commands | wc" to count correctly. It's okay to count * the USE_READLINE line even in builds without that. */ - output = PageOutput(110, pager ? &(pset.popt.topt) : NULL); + output = PageOutput(111, pager ? &(pset.popt.topt) : NULL); fprintf(output, _("General\n")); fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n")); fprintf(output, _(" \\errverbose show most recent error message at maximum verbosity\n")); fprintf(output, _(" \\g [FILE] or ; execute query (and send results to file or |pipe)\n")); + fprintf(output, _(" \\gexec execute query, then execute each value in its result\n")); fprintf(output, _(" \\gset [PREFIX] execute query and store results in psql variables\n")); fprintf(output, _(" \\q quit psql\n")); fprintf(output, _(" \\watch [SEC] execute query every SEC seconds\n")); diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h index ae30b2e60e3..c69f6ba1ec4 100644 --- a/src/bin/psql/settings.h +++ b/src/bin/psql/settings.h @@ -92,6 +92,7 @@ typedef struct _psqlSettings char *gfname; /* one-shot file output argument for \g */ char *gset_prefix; /* one-shot prefix argument for \gset */ + bool gexec_flag; /* one-shot flag to execute query's results */ bool notty; /* stdin or stdout is not a tty (as determined * on startup) */ diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 688d92a4520..cb8a06d15e4 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1281,7 +1281,7 @@ psql_completion(const char *text, int start, int end) "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\drds", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy", "\\e", "\\echo", "\\ef", "\\encoding", "\\errverbose", "\\ev", - "\\f", "\\g", "\\gset", "\\h", "\\help", "\\H", "\\i", "\\ir", "\\l", + "\\f", "\\g", "\\gexec", "\\gset", "\\h", "\\help", "\\H", "\\i", "\\ir", "\\l", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink", "\\o", "\\p", "\\password", "\\prompt", "\\pset", "\\q", "\\qecho", "\\r", "\\s", "\\set", "\\setenv", "\\sf", "\\sv", "\\t", "\\T", diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 178a8093b73..edcc414630c 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -52,6 +52,51 @@ more than one row returned for \gset select 10 as test01, 20 as test02 from generate_series(1,0) \gset no rows returned for \gset \unset FETCH_COUNT +-- \gexec +create temporary table gexec_test(a int, b text, c date, d float); +select format('create index on gexec_test(%I)', attname) +from pg_attribute +where attrelid = 'gexec_test'::regclass and attnum > 0 +order by attnum +\gexec +create index on gexec_test(a) +create index on gexec_test(b) +create index on gexec_test(c) +create index on gexec_test(d) +-- \gexec should work in FETCH_COUNT mode too +-- (though the fetch limit applies to the executed queries not the meta query) +\set FETCH_COUNT 1 +select 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)' +union all +select 'drop table gexec_test', NULL +union all +select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over' +\gexec +select 1 as ones + ones +------ + 1 +(1 row) + +select x.y, x.y*2 as double from generate_series(1,4) as x(y) + y | double +---+-------- + 1 | 2 + 2 | 4 + 3 | 6 + 4 | 8 +(4 rows) + +drop table gexec_test +drop table gexec_test +ERROR: table "gexec_test" does not exist +select '2000-01-01'::date as party_over + party_over +------------ + 01-01-2000 +(1 row) + +\unset FETCH_COUNT -- show all pset options \pset border 1 diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index 2f81380e226..c5b36649491 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -38,6 +38,28 @@ select 10 as test01, 20 as test02 from generate_series(1,0) \gset \unset FETCH_COUNT +-- \gexec + +create temporary table gexec_test(a int, b text, c date, d float); +select format('create index on gexec_test(%I)', attname) +from pg_attribute +where attrelid = 'gexec_test'::regclass and attnum > 0 +order by attnum +\gexec + +-- \gexec should work in FETCH_COUNT mode too +-- (though the fetch limit applies to the executed queries not the meta query) +\set FETCH_COUNT 1 + +select 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)' +union all +select 'drop table gexec_test', NULL +union all +select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over' +\gexec + +\unset FETCH_COUNT + -- show all pset options \pset |