psql metaqueries with \gexec
Often, I'm faced with a long .sql script that builds some objects, then
builds things on top of them.
This means that some of the queries I wish to run are dependent on the
state of things that are unknown at the time of writing the script.
I could give up, and make a python script that mostly just strings together
SQL statements. That's ugly and cumbersome.
I could do some wizardry like this:
$ create table foo( a integer, b text, c date);
$ select coalesce( ( select string_agg(format('create index
foo(%I);',attname),E'\n')
from pg_attribute
where attrelid = 'foo'::regclass
and attnum > 0 order by attnum),
'') as sql_statements
\gset
:sql_statements
For those of you not willing to parse that, that's a dictionary query with
a 1-column result set formatted into sql with a ';' appended, string
aggregated with a newline delimiter, with the final result set coalesced
with an empty string because \gset will error on an empty result set. I
then immediately put that psql variable back into the command buffer, where
I hope that I meta-wrote valid SQL. If it hurt to read, you can imagine
what it was like to write.
I could use \g and pipe the results to another psql session...but that will
happen in another transaction where my objects might not exist yet.
I would also like the log to show what commands were run.
For that reason, I created the psql command \gexec
It is like \g and \gset in the sense that it executes the query currently
in the buffer. However, it treats every cell in the result set as a query
which itself should be immediately executed.
$ create temporary table gexec_temp( a int, b text, c date, d float);
CREATE TABLE
$ select format('create index on gexec_temp(%I)',attname)
from pg_attribute
where attrelid = 'gexec_temp'::regclass
and attnum > 0
order by attnum
\gexec
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
Execution order of the statements is top to bottom, left to right.
$ 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)
Empty result sets do nothing:
$ select 'select 1 as expect_zero_rows ' where false
\gexec
The results are just strings which are sent to SendQuery(), where they
succeed or fail on their own merits
$ select 'do $$ begin raise notice ''plpgsql block executed''; end;$$' as
block
from generate_series(1,2)
\gexec
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
I am not sure that "gexec" is the right name for this command. Others
considered were \execute_each, \meta, \gmeta, \geach, as well as adding a
"<" parameter to the \g command.
Many thanks to Pavel Stěhule for giving me some direction in this endeavor,
though he might not agree with the design.
Attachments:
psql_gexec_v1.difftext/plain; charset=US-ASCII; name=psql_gexec_v1.diffDownload
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..35bbeb9 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -710,6 +710,39 @@ 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)) )
+ {
+ success = false;
+ }
+ }
+ }
+ }
+
+ /* Return true if all queries were successful */
+ return success;
+}
+
+
/*
* ProcessResult: utility function for use by SendQuery() only
@@ -903,8 +936,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 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",
"\\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..20f86b4 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -2665,3 +2665,64 @@ 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
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 2f81380..4626541 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -351,3 +351,36 @@ 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
On 2/19/16 7:32 PM, Corey Huinker wrote:
Wow, and I thought *I* liked metaprogramming! :)
I like what you've proposed, though I am wondering if you considered
doing something server-side instead? It seems a shame to do all this
work and exclude all other tools.
I frequently find myself creating a function that is just a wrapper on
EXECUTE for this purpose, but obviously that has transactional limitations.
FWIW, I also wish we had something better than format() for this stuff.
I did create [1]https://github.com/decibel/trunklet-format/blob/master/doc/trunklet-format.asc -- 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 towards that end, but it currently depends on some C
code, which is cumbersome.
I am not sure that "gexec" is the right name for this command. Others
considered were \execute_each, \meta, \gmeta, \geach, as well as adding
a "<" parameter to the \g command.
\gexec sounds fine to me. I would think \g < would be something done at
the shell level...
[1]: https://github.com/decibel/trunklet-format/blob/master/doc/trunklet-format.asc -- 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
https://github.com/decibel/trunklet-format/blob/master/doc/trunklet-format.asc
--
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I like what you've proposed, though I am wondering if you considered doing
something server-side instead? It seems a shame to do all this work and
exclude all other tools.
I have, but my solutions closely mirror the one you mention in the next
paragraph.
I frequently find myself creating a function that is just a wrapper on
EXECUTE for this purpose, but obviously that has transactional limitations.
...and query text visibility, and result visibility, and error handling,
etc. In this case, we're leveraging the psql environment we'd already set
up, and if there's an error, \set ECHO queries shows us the errant SQL as
if we typed it ourselves..
FWIW, I also wish we had something better than format() for this stuff. I
did create [1] towards that end, but it currently depends on some C code,
which is cumbersome.
For the most party, I'm pretty thrilled with format(), though:
- I'll admit to being grumpy about the %1$s notation, but I have no better
suggestion.
- I'd also like it if there were a %I variant that accepted schema
qualified names and %I-ed both, though I see the inability to tell the
difference between a schema dot and a really-named-that dot.
- I'd love it if there were a %C format that took a pg_class oid and
formatted the qualified schema name. As it is i just use %s and cast the
parameter as ::regclass
[1]
https://github.com/decibel/trunklet-format/blob/master/doc/trunklet-format.asc
That's intense. I'll ask you about that in an off-list thread.
FWIW, I also wish we had something better than format() for this stuff. I
did create [1] towards that end, but it currently depends on some C code,
which is cumbersome.
For the most party, I'm pretty thrilled with format(), though:
- I'll admit to being grumpy about the %1$s notation, but I have no better
suggestion.
- I'd also like it if there were a %I variant that accepted schema
qualified names and %I-ed both, though I see the inability to tell the
difference between a schema dot and a really-named-that dot.
- I'd love it if there were a %C format that took a pg_class oid and
formatted the qualified schema name. As it is i just use %s and cast the
parameter as ::regclass
The design of the "format" function is not closed. Try to send prototype
and patch. The possibility to do PostgreSQL customization was strong reason
why we didn't implemented "sprintf" and we implemented "format".
Regards
Pavel
Corey Huinker wrote:
...and query text visibility, and result visibility, and error handling,
etc. In this case, we're leveraging the psql environment we'd already set
up, and if there's an error, \set ECHO queries shows us the errant SQL as
if we typed it ourselves..
BTW, about error handling, shouldn't it honor ON_ERROR_STOP ?
With the patch when trying this:
=> set ON_ERROR_STOP on
=> select * from (values ('select 1/0', 'select 1/0')) AS n \gexec
it produces two errors:
ERROR: division by zero
ERROR: division by zero
I'd rather have the execution stop immediately after the first error,
like it's the case with successive queries entered normally via the
query buffer:
=> \set ON_ERROR_STOP on
=> select 1/0; select 1/0;
ERROR: division by zero
as opposed to:
=> \set ON_ERROR_STOP off
=> select 1/0; select 1/0;
ERROR: division by zero
ERROR: division by zero
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Feb 22, 2016 at 10:08 AM, Daniel Verite <daniel@manitou-mail.org>
wrote:
Corey Huinker wrote:
...and query text visibility, and result visibility, and error handling,
etc. In this case, we're leveraging the psql environment we'd already set
up, and if there's an error, \set ECHO queries shows us the errant SQL as
if we typed it ourselves..BTW, about error handling, shouldn't it honor ON_ERROR_STOP ?
With the patch when trying this:
=> set ON_ERROR_STOP on
=> select * from (values ('select 1/0', 'select 1/0')) AS n \gexecit produces two errors:
ERROR: division by zero
ERROR: division by zeroI'd rather have the execution stop immediately after the first error,
like it's the case with successive queries entered normally via the
query buffer:=> \set ON_ERROR_STOP on
=> select 1/0; select 1/0;
ERROR: division by zeroas opposed to:
=> \set ON_ERROR_STOP off
=> select 1/0; select 1/0;
ERROR: division by zero
ERROR: division by zero
Yes, I would like it to honor ON_ERROR_STOP. I'll look into that.
Pavel Stehule wrote:
The design of the "format" function is not closed. Try to send prototype
and patch. The possibility to do PostgreSQL customization was strong reason
why we didn't implemented "sprintf" and we implemented "format".
Probably not terribly useful here, but for the DDL-deparse patch I came
up with a syntax to format JSON objects, which used %-escapes; each
escaped element corresponds to a string literal, or to an object. So
you'd have %{table}D where the "table" element in the JSON object could
be a simple string which is expanded verbatim (plus quoting if
necessary), or it could be a JSON object with something like { schema =>
"public", name => "students" }, where each element is expanded and
quoted as necessary; if the "schema" is null or it doesn't exist, it
expands only the name, obviously omitting the dot.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Feb 22, 2016 at 11:30 AM, Corey Huinker <corey.huinker@gmail.com>
wrote:
On Mon, Feb 22, 2016 at 10:08 AM, Daniel Verite <daniel@manitou-mail.org>
wrote:Corey Huinker wrote:
...and query text visibility, and result visibility, and error handling,
etc. In this case, we're leveraging the psql environment we'd alreadyset
up, and if there's an error, \set ECHO queries shows us the errant SQL
as
if we typed it ourselves..
BTW, about error handling, shouldn't it honor ON_ERROR_STOP ?
With the patch when trying this:
=> set ON_ERROR_STOP on
=> select * from (values ('select 1/0', 'select 1/0')) AS n \gexecit produces two errors:
ERROR: division by zero
ERROR: division by zeroI'd rather have the execution stop immediately after the first error,
like it's the case with successive queries entered normally via the
query buffer:=> \set ON_ERROR_STOP on
=> select 1/0; select 1/0;
ERROR: division by zeroas opposed to:
=> \set ON_ERROR_STOP off
=> select 1/0; select 1/0;
ERROR: division by zero
ERROR: division by zeroYes, I would like it to honor ON_ERROR_STOP. I'll look into that.
Well, that was easy enough. Turns out that pset.on_error_stop is checked in
MainLoop, whereas the other pset.on_* vars are checked in SendQuery().
My original idea had been to push each cell into a in-memory temp file
handle and call MainLoop() on each. Pavel suggested that temp files of any
sort were a bad idea, hence using SendQuery instead. It's probably for the
best.
# select 'select 1,2,3', 'select 1/0', 'select 4,5,6'
... # \gexec
?column? | ?column? | ?column?
----------+----------+----------
1 | 2 | 3
(1 row)
Time: 0.151 ms
ERROR: 22012: division by zero
LOCATION: int4div, int.c:719
Time: 0.528 ms
?column? | ?column? | ?column?
----------+----------+----------
4 | 5 | 6
(1 row)
Time: 0.139 ms
Time: 0.595 ms
# \set ON_ERROR_STOP 1
# select 'select 1,2,3', 'select 1/0', 'select 4,5,6' \gexec
?column? | ?column? | ?column?
----------+----------+----------
1 | 2 | 3
(1 row)
Time: 0.137 ms
ERROR: 22012: division by zero
LOCATION: int4div, int.c:719
Time: 0.165 ms
Time: 0.284 ms
Does \set ON_ERROR_STOP mess up regression tests? If not, I'll add the test
above (minus the \set VERBOSITY verbose-isms) to the regression.
In the mean time, update patch attached.
In the mean time, update patch attached.
Really attached this time.
Attachments:
psql_gexec_v2.difftext/plain; charset=US-ASCII; name=psql_gexec_v2.diffDownload
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 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",
"\\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..20f86b4 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -2665,3 +2665,64 @@ 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
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 2f81380..4626541 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -351,3 +351,36 @@ 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
On 2/22/16 11:47 AM, Alvaro Herrera wrote:
Pavel Stehule wrote:
The design of the "format" function is not closed. Try to send prototype
and patch. The possibility to do PostgreSQL customization was strong reason
why we didn't implemented "sprintf" and we implemented "format".Probably not terribly useful here, but for the DDL-deparse patch I came
up with a syntax to format JSON objects, which used %-escapes; each
escaped element corresponds to a string literal, or to an object. So
you'd have %{table}D where the "table" element in the JSON object could
be a simple string which is expanded verbatim (plus quoting if
necessary), or it could be a JSON object with something like { schema =>
"public", name => "students" }, where each element is expanded and
quoted as necessary; if the "schema" is null or it doesn't exist, it
expands only the name, obviously omitting the dot.
Where did the "D" in "%{table}D" come from?
BTW, the syntax I chose for [1]https://github.com/decibel/trunklet-format/blob/master/doc/trunklet-format.asc#2-template-specification -- 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 is similar to format's, except I elected
to stick with % instead of $. So you do
%parameter_name%type
where type is s, L or I. I don't think it'd be hard to support an object
with 'schema' and 'name' keys.
[1]: https://github.com/decibel/trunklet-format/blob/master/doc/trunklet-format.asc#2-template-specification -- 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
https://github.com/decibel/trunklet-format/blob/master/doc/trunklet-format.asc#2-template-specification
--
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jim Nasby wrote:
On 2/22/16 11:47 AM, Alvaro Herrera wrote:
Pavel Stehule wrote:
The design of the "format" function is not closed. Try to send prototype
and patch. The possibility to do PostgreSQL customization was strong reason
why we didn't implemented "sprintf" and we implemented "format".Probably not terribly useful here, but for the DDL-deparse patch I came
up with a syntax to format JSON objects, which used %-escapes; each
escaped element corresponds to a string literal, or to an object. So
you'd have %{table}D where the "table" element in the JSON object could
be a simple string which is expanded verbatim (plus quoting if
necessary), or it could be a JSON object with something like { schema =>
"public", name => "students" }, where each element is expanded and
quoted as necessary; if the "schema" is null or it doesn't exist, it
expands only the name, obviously omitting the dot.Where did the "D" in "%{table}D" come from?
The I in %{foo}I was for "identifier" (of course) and I *think* the D
was for "double identifiers" (that is, qualified). I expanded the idea
afterwards to allow for a third name for things like
catalog.schema.name, so I guess it's a misnomer already.
It's not released code yet. You can see an example here
/messages/by-id/%3C20150224175152.GI5169@alvh.no-ip.org%3E
just scroll down a few hundred lines to about 7/16ths of the page (yes,
really)
(One thing I had to come up with was processing of arrays, which you
also see in that example JSON -- it's the specifiers that have a colon
inside the {}. The part after the colon is used as separator between
the array elements, and each element is expanded separately.)
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2/22/16 5:13 PM, Alvaro Herrera wrote:
Jim Nasby wrote:
On 2/22/16 11:47 AM, Alvaro Herrera wrote:
Pavel Stehule wrote:
The design of the "format" function is not closed. Try to send prototype
and patch. The possibility to do PostgreSQL customization was strong reason
why we didn't implemented "sprintf" and we implemented "format".Probably not terribly useful here, but for the DDL-deparse patch I came
up with a syntax to format JSON objects, which used %-escapes; each
escaped element corresponds to a string literal, or to an object. So
you'd have %{table}D where the "table" element in the JSON object could
be a simple string which is expanded verbatim (plus quoting if
necessary), or it could be a JSON object with something like { schema =>
"public", name => "students" }, where each element is expanded and
quoted as necessary; if the "schema" is null or it doesn't exist, it
expands only the name, obviously omitting the dot.Where did the "D" in "%{table}D" come from?
The I in %{foo}I was for "identifier" (of course) and I *think* the D
was for "double identifiers" (that is, qualified). I expanded the idea
afterwards to allow for a third name for things like
catalog.schema.name, so I guess it's a misnomer already.It's not released code yet. You can see an example here
/messages/by-id/%3C20150224175152.GI5169@alvh.no-ip.org%3E
just scroll down a few hundred lines to about 7/16ths of the page (yes,
really)(One thing I had to come up with was processing of arrays, which you
also see in that example JSON -- it's the specifiers that have a colon
inside the {}. The part after the colon is used as separator between
the array elements, and each element is expanded separately.)
Ahh, very interesting.
Something that would probably be helpful for these kind of things is if
we had a set of complex types available that represented things like the
arguments to a function. Something like (parameter_mode enum(IN, OUT,
INOUT), parameter_name name, parameter_type regtype, parameter_default
text). A function might be represented by (function_schema name,
function_name name, function_parameters ..., function_language,
function_options, function_body).
In any case, having anything along these lines in core would be useful,
assuming that the individual facility was exposed as well (as opposed to
only being available inside an event trigger).
--
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
Attachments:
psql_gexec_v3.difftext/plain; charset=UTF-8; name=psql_gexec_v3.diff; x-mac-creator=0; x-mac-type=0Download
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.
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not tested
Still needs documentation.
The new status of this patch is: Waiting on Author
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.cindex 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
Attachments:
psql_gexec_v4.difftext/plain; charset=US-ASCII; name=psql_gexec_v4.diffDownload
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.
On Mon, Mar 14, 2016 at 7:54 AM, Corey Huinker <corey.huinker@gmail.com> wrote:
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
Jim, can you re-review this?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
Jim, can you re-review this?
I'm not Jim, but I have a question: what's the motivation for the
Fortran-order traversal of the result (down rows before across columns)?
It seems less than intuitive to do it that way. Perhaps there's a good
reason, but I do not see any defense of this choice in the thread.
I also note that the patch seems to be missing resetting gexec_flag
in some error exit paths, possibly allowing the \gexec to be applied
to the next query unexpectedly. It should clear that in all the same
places where gfname or gset_prefix get cleared.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Apr 3, 2016 at 4:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Jim, can you re-review this?
I'm not Jim, but I have a question: what's the motivation for the
Fortran-order traversal of the result (down rows before across columns)?
It seems less than intuitive to do it that way. Perhaps there's a good
reason, but I do not see any defense of this choice in the thread.
If I am understanding you correctly, it does work the way you find
intuitive: all results from the first row are executed before any in the
second row, so
SELECT a, b UNION ALL SELECT c, d
will execute the queries in order: a, b, c, d as is shown in the changes to
the sgml and the test cases.
Did you get the impression of Fortran-ordering from the phrase
"top-to-bottom, left-to-right order" in the sgml patch? If so, would
calling it "rows first" or something else be more clear?
Or am I misunderstanding you and you find the order a, c, b, d more
intuitive?
I also note that the patch seems to be missing resetting gexec_flag
in some error exit paths, possibly allowing the \gexec to be applied
to the next query unexpectedly. It should clear that in all the same
places where gfname or gset_prefix get cleared.
Will do!
I'm only seeing one place where those two vars are deallocated and nulled,
and that's at the tail end of SendQuery. Were you expecting more than just
that?
Corey Huinker <corey.huinker@gmail.com> writes:
On Sun, Apr 3, 2016 at 4:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'm not Jim, but I have a question: what's the motivation for the
Fortran-order traversal of the result (down rows before across columns)?
If I am understanding you correctly, it does work the way you find
intuitive: all results from the first row are executed before any in the
second row, so
Oh, I hadn't checked the code closely enough to realize that, but I see
you're right. The patch's documentation seems very confusing on the
point, though:
+ 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.
It should clear that in all the same
places where gfname or gset_prefix get cleared.
I'm only seeing one place where those two vars are deallocated and nulled,
and that's at the tail end of SendQuery. Were you expecting more than just
that?
That may be the only place; I've not looked around.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
+ The secondary queries are executed in top-to-bottom,
left-to-right order, so the commandI took that as meaning what I said above.
Would using the term https://en.wikipedia.org/wiki/Row-major_order be more
clear?
The secondary queries are executed in row-major order, so the
command...
If so, it will probably aide in translation as well.
It should clear that in all the same
places where gfname or gset_prefix get cleared.I'm only seeing one place where those two vars are deallocated and
nulled,
and that's at the tail end of SendQuery. Were you expecting more than
just
that?
That may be the only place; I've not looked around.
Yeah, seems that there might have been multiple ones in the past, but all
paths now funnel through the sendquery_cleanup: goto.
Corey Huinker <corey.huinker@gmail.com> 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Apr 3, 2016 at 7:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Corey Huinker <corey.huinker@gmail.com> writes:
+ The secondary queries are executed in top-to-bottom,
left-to-right order, so the commandI 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.
On Sun, Apr 3, 2016 at 8:42 PM, Corey Huinker <corey.huinker@gmail.com>
wrote:
On Sun, Apr 3, 2016 at 7:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Corey Huinker <corey.huinker@gmail.com> writes:
+ The secondary queries are executed in top-to-bottom,
left-to-right order, so the commandI 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
Attachments:
psql_gexec_v5.difftext/plain; charset=US-ASCII; name=psql_gexec_v5.diffDownload
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.
Corey Huinker <corey.huinker@gmail.com> writes:
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.
I whacked this around some and committed it. The main thing that was
broken is that it didn't work nicely at all if you'd set FETCH_COUNT.
I experimented with different approaches to that, and ultimately decided
that the best answer is to disable use of ExecQueryUsingCursor for the
\gexec master query. We can still let it be used for the individual
generated queries, though.
I didn't much like the regression test setup, either. Tests that
have to be at the end of their test files aren't very nice, unless
you give them their very own test file, which checking ON_ERROR_STOP
didn't seem worth. To me it's far more important that the code
respond to cancel_pressed (which, ahem, it wasn't) and we have no
mechanism for testing that in a pg_regress script. So I just dropped
that aspect of it and put the test in a more logical place in the file.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Apr 4, 2016 at 3:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Corey Huinker <corey.huinker@gmail.com> writes:
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.I whacked this around some and committed it. The main thing that was
Hooray!
broken is that it didn't work nicely at all if you'd set FETCH_COUNT.
Mmm, yeah, I hadn't considered cursor fetches, but in the use cases (at
least the ones I can imagine for this) you wouldn't want fetches.
I experimented with different approaches to that, and ultimately decided
that the best answer is to disable use of ExecQueryUsingCursor for the
\gexec master query. We can still let it be used for the individual
generated queries, though.
Fine by me.
I didn't much like the regression test setup, either. Tests that
have to be at the end of their test files aren't very nice, unless
you give them their very own test file, which checking ON_ERROR_STOP
didn't seem worth. To me it's far more important that the code
respond to cancel_pressed (which, ahem, it wasn't) and we have no
mechanism for testing that in a pg_regress script. So I just dropped
that aspect of it and put the test in a more logical place in the file.
I think it was Jim that added the ON_ERROR_STOP check. I wasn't sure how to
properly test that.
Thanks for finding (and fixing) the cancel_pressed issue.