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.
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
