On Sun, Apr 3, 2016 at 8:42 PM, Corey Huinker <[email protected]>
wrote:
> On Sun, Apr 3, 2016 at 7:43 PM, Tom Lane <[email protected]> wrote:
>
>> Corey Huinker <[email protected]> writes:
>> >>> + The secondary queries are executed in top-to-bottom,
>> >>> left-to-right order, so the command
>>
>> >> I took that as meaning what I said above.
>>
>> > Would using the term https://en.wikipedia.org/wiki/Row-major_order be
>> more
>> > clear?
>>
>> Meh, I suspect a lot of people don't know that term. Perhaps something
>> like "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."
>>
>> regards, tom lane
>>
>
>
> I like it. Change forthcoming.
>
Changes since last submission:
Patch attached. Changes are thus:
- rebased
- pset.gexec_flag unconditionally set to false at end of SendQuery
- wording of documentation describing execution order of results
- rebasing allowed for undoing the re-wrap of enumerated slash commands.
Still not changed:
- exuberant braces, can remove if someone wants me to
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index e8afc24..1fb4b55 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1767,6 +1767,92 @@ Tue Oct 26 21:40:57 CEST 1999
</varlistentry>
<varlistentry>
+ <term><literal>\gexec</literal></term>
+
+ <listitem>
+ <para>
+ Sends the current query input buffer to the server and treats
+ every column of every row of query output (if any) as a separate
+ SQL statement to be immediately executed. For example:
+<programlisting>
+=> <userinput>SELECT 'select 1 as ones', 'select x.y, x.y*2 as double from
generate_series(1,4) as x(y)'</userinput>
+-> <userinput>UNION ALL</userinput>
+-> <userinput>SELECT 'select true as is_true', 'select ''2000-01-01''::date
as party_over'</userinput>
+-> <userinput>\gexec</userinput>
+ones
+----
+ 1
+(1 row)
+
+y double
+- ------
+1 2
+2 4
+3 6
+4 8
+(4 rows)
+
+is_true
+-------
+t
+(1 row)
+
+party_over
+----------
+01-01-2000
+(1 row)
+</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. So,
the command
+ above is the equivalent of:
+<programlisting>
+=> <userinput>select 1 as ones;</userinput>
+=> <userinput>select x.y, x.y*2 as double from generate_series(1,4) as
x(y);</userinput>
+=> <userinput>select true as is_true;</userinput>
+=> <userinput>select '2000-01-01'::date as party_over;</userinput>
+</programlisting>
+ </para>
+ <para>
+ If the query returns no rows, no error is raised, but no secondary
query
+ is executed, either.
+<programlisting>
+=%gt; <userinput>SELECT 'select 1 as expect_zero_rows ' where false
+-> <userinput>\gexec</userinput>
+
+</programlisting>
+ </para>
+ <para>
+ Results that are not valid SQL will of course fail, and the execution
of further
+ secondary statements is subject to the current \ON_ERROR_STOP setting.
+<programlisting>
+=> <userinput>SELECT 'a', 'select 1', 'b'</userinput>
+-> <userinput>\gexec</userinput>
+ERROR: syntax error at or near "a"
+LINE 1: a
+ ^
+?column?
+--------
+ 1
+(1 row)
+ERROR: syntax error at or near "b"
+LINE 1: b
+ ^
+=> <userinput>\set ON_ERROR_STOP 1</userinput>
+=> <userinput>SELECT 'a', 'select 1', 'b'</userinput>
+-> <userinput>\gexec</userinput>
+ERROR: syntax error at or near "a"
+LINE 1: a
+ ^
+</programlisting>
+ <para>
+ The results of the main query are sent directly to the server, without
+ evaluation by psql. Therefore, they cannot contain psql vars or \
commands.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
<term><literal>\gset [ <replaceable
class="parameter">prefix</replaceable> ]</literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 3401b51..1baff8e 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 treat every result cell as a query to be
executed */
+ 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 a2a07fb..0db5de2 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -796,6 +796,46 @@ StoreQueryTuple(const PGresult *result)
return success;
}
+/*
+ * ExecQueryTuples: assuming query result is OK, execute every query
+ * result as its own 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;
+
+ for (r = 0; r < nrows; r++)
+ {
+ for (c = 0; c < ncolumns; c++)
+ {
+ if (! PQgetisnull(result, r, c))
+ {
+ if ( ! SendQuery(PQgetvalue(result, r, c)) )
+ {
+ if (pset.on_error_stop)
+ {
+ return false;
+ }
+ else
+ {
+ success = false;
+ }
+ }
+ }
+ }
+ }
+
+ /* Return true if all queries were successful */
+ return success;
+}
+
+
/*
* ProcessResult: utility function for use by SendQuery() only
@@ -989,8 +1029,14 @@ PrintQueryResults(PGresult *results)
switch (PQresultStatus(results))
{
case PGRES_TUPLES_OK:
- /* store or print the data ... */
- if (pset.gset_prefix)
+ /* execute or store or print the data ... */
+ if (pset.gexec_flag)
+ {
+ /* Turn off gexec_flag to avoid infinite loop */
+ pset.gexec_flag = false;
+ success = ExecQueryTuples(results);
+ }
+ else if (pset.gset_prefix)
success = StoreQueryTuple(results);
else
success = PrintQueryTuples(results);
@@ -1278,6 +1324,8 @@ sendquery_cleanup:
pset.gset_prefix = NULL;
}
+ pset.gexec_flag = false;
+
return OK;
}
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index c6f0993..3b119c3 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -174,6 +174,7 @@ slashUsage(unsigned short int pager)
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 and treat
every result cell as a query to be executed )\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 ae30b2e..2dd3936 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -93,6 +93,9 @@ typedef struct _psqlSettings
char *gfname; /* one-shot file output
argument for \g */
char *gset_prefix; /* one-shot prefix argument for \gset */
+ bool gexec_flag; /* true if query results are to
be treated as
+ * queries to
be executed. Set by \gexec */
+
bool notty; /* stdin or stdout is not a tty
(as determined
* on startup)
*/
enum trivalue getPassword; /* prompt the user for a username and
password */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 688d92a..cb8a06d 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 178a809..b6d1d83 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -2665,3 +2665,84 @@ NOTICE: foo
CONTEXT: PL/pgSQL function inline_code_block line 3 at RAISE
ERROR: bar
CONTEXT: PL/pgSQL function inline_code_block line 4 at RAISE
+-- \gexec
+-- restore relevant display settings
+\set SHOW_CONTEXT never
+\pset format aligned
+\pset expanded off
+\pset border 0
+\set QUIET 0
+-- should execute four separate trivial queries
+select 'select 1 as ones', 'select x.y, x.y*2 as double from
generate_series(1,4) as x(y)'
+union all
+select 'select true as is_true', 'select ''2000-01-01''::date as party_over'
+\gexec
+ones
+----
+ 1
+(1 row)
+
+y double
+- ------
+1 2
+2 4
+3 6
+4 8
+(4 rows)
+
+is_true
+-------
+t
+(1 row)
+
+party_over
+----------
+01-01-2000
+(1 row)
+
+--
+create temporary table gexec_temp( a int, b text, c date, d float);
+CREATE TABLE
+\set ECHO queries
+select format('create index on gexec_temp(%I)',attname)
+from pg_attribute
+where attrelid = 'gexec_temp'::regclass
+and attnum > 0
+order by attnum
+create index on gexec_temp(a)
+CREATE INDEX
+create index on gexec_temp(b)
+CREATE INDEX
+create index on gexec_temp(c)
+CREATE INDEX
+create index on gexec_temp(d)
+CREATE INDEX
+select 'select 1 as expect_zero_rows ' where false
+select 'do $$ begin raise notice ''plpgsql block executed''; end;$$' as block
+from generate_series(1,2)
+do $$ begin raise notice 'plpgsql block executed'; end;$$
+NOTICE: plpgsql block executed
+DO
+do $$ begin raise notice 'plpgsql block executed'; end;$$
+NOTICE: plpgsql block executed
+DO
+select 'a', 'select 1', 'b'
+a
+ERROR: syntax error at or near "a"
+LINE 1: a
+ ^
+select 1
+?column?
+--------
+ 1
+(1 row)
+
+b
+ERROR: syntax error at or near "b"
+LINE 1: b
+ ^
+select 'a', 'select 1', 'b'
+a
+ERROR: syntax error at or near "a"
+LINE 1: a
+ ^
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 2f81380..6278b40 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -351,3 +351,47 @@ begin
raise notice 'foo';
raise exception 'bar';
end $$;
+
+-- \gexec
+-- restore relevant display settings
+\set SHOW_CONTEXT never
+\pset format aligned
+\pset expanded off
+\pset border 0
+\set QUIET 0
+-- should execute four separate trivial queries
+select 'select 1 as ones', 'select x.y, x.y*2 as double from
generate_series(1,4) as x(y)'
+union all
+select 'select true as is_true', 'select ''2000-01-01''::date as party_over'
+\gexec
+
+--
+create temporary table gexec_temp( a int, b text, c date, d float);
+
+\set ECHO queries
+select format('create index on gexec_temp(%I)',attname)
+from pg_attribute
+where attrelid = 'gexec_temp'::regclass
+and attnum > 0
+order by attnum
+\gexec
+
+-- should not get any rows at all
+select 'select 1 as expect_zero_rows ' where false
+\gexec
+
+-- test anonymous blocks
+select 'do $$ begin raise notice ''plpgsql block executed''; end;$$' as block
+from generate_series(1,2)
+\gexec
+
+-- test multiple failures without ON_ERROR_STOP
+select 'a', 'select 1', 'b'
+\gexec
+
+-- test multiple failures with ON_ERROR_STOP
+\set ON_ERROR_STOP 1
+select 'a', 'select 1', 'b'
+\gexec
+
+-- DO NOT ADD ANY TESTS AFTER THIS! They will not run due to the ON_ERROR_STOP
test we just ran.
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
