>
>
> I'm getting a warning from this patch:
>
> 1 warning generated.
>
Fixed that one.
(note that I'm using CC='ccache clang -Qunused-arguments
> -fcolor-diagnostics')
>
> for (r = 0; r < nrows; r++)
>> {
>> for (c = 0; c < ncolumns; c++)
>> {
>>
> etc...
>
> Normally we don't use gratuitous {'s, and I don't think it's helping
> anything in this case. But I'll let whoever commits this decide.
>
Good to know in the future. I can remove or leave to the committer.
> diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
>> index 5f27120..0f87f29 100644
>> --- a/src/bin/psql/tab-complete.c
>> +++ b/src/bin/psql/tab-complete.c
>> @@ -1280,8 +1280,8 @@ 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", "\\ev",
>> - "\\f", "\\g", "\\gset", "\\h", "\\help", "\\H", "\\i",
>> "\\ir", "\\l",
>> - "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
>> + "\\f", "\\g", "\\gexec", "\\gset", "\\h", "\\help",
>> "\\H", "\\i", "\\ir",
>> + "\\l", "\\lo_import", "\\lo_export", "\\lo_list",
>> "\\lo_unlink",
>>
>
> FWIW, it's generally better to leave that kind of re-wrapping to the next
> pg_indent run.
>
Good to know in the future. Not much point in undoing it now, I suppose.
>
> I added tests for ON_ERROR_STOP. New patch attached.
>
I was wondering if ON_ERROR_STOP tests were verbotten because you only get
to kick the tires on one feature...
>
> The patch still needs to document this feature in the psql docs (and maybe
> the manpage? not sure how that's generated...)
doc/src/sgml/ref/psql-ref.sgml is the source for both html and man pagers.
I'm on it. I didn't expect the name "gexec" to survive first contact with
the community.
Patch attached. Changes are thus:
- proper assignment of success var
- added documentation to psql manpage/html with examples pulled from
regression tests.
Not changed are:
- exuberant braces, can remove if someone wants me to
- attempt at line-wrappng the enumerated slash commands, leave that to
pg_indent
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 8a85804..acb0eb7 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1753,6 +1753,91 @@ 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 secondary queries are executed in top-to-bottom, left-to-right
order, 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 9750a5b..5ca769f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -849,6 +849,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 2cb2e9b..3d6f3cf 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -710,6 +710,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
@@ -903,8 +943,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);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 59f6f25..251dd1e 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -173,6 +173,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _("General\n"));
fprintf(output, _(" \\copyright show PostgreSQL usage and
distribution terms\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 20a6470..9f1e94b 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -91,6 +91,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 6a81416..9f50751 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1280,8 +1280,8 @@ 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", "\\ev",
- "\\f", "\\g", "\\gset", "\\h", "\\help", "\\H", "\\i", "\\ir",
"\\l",
- "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
+ "\\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",
"\\timing", "\\unset", "\\x", "\\w", "\\watch", "\\z", "\\!",
NULL
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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
