On 2/22/16 1:01 PM, Corey Huinker wrote:
In the mean time, update patch attached.
Really attached this time.
I'm getting a warning from this patch:
common.c:947:8: warning: variable 'success' is used uninitialized
whenever 'if' condition is true [-Wsometimes-uninitialized]
if (pset.gexec_flag)
^~~~~~~~~~~~~~~
common.c:995:9: note: uninitialized use occurs here
return success;
^~~~~~~
common.c:947:4: note: remove the 'if' if its condition is always false
if (pset.gexec_flag)
^~~~~~~~~~~~~~~~~~~~
common.c:937:15: note: initialize the variable 'success' to silence this
warning
bool success;
^
= '\0'
1 warning generated.
(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.
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.
I added tests for ON_ERROR_STOP. New patch attached.
The patch still needs to document this feature in the psql docs (and
maybe the manpage? not sure how that's generated...)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
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..54b7790 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;
+ 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
