On 4th of April, Tom Lane committed patch:
Add a \gexec command to psql for evaluation of computed queries. \gexec executes the just-entered query, like \g, but instead of printing the results it takes each field as a SQL command to send to the server. Computing a series of queries to be executed is a fairly common thing, but up to now you always had to resort to kluges like writing the queries to a file and then inputting the file. Now it can be done with no intermediate step. The implementation is fairly straightforward except for its interaction with FETCH_COUNT. ExecQueryUsingCursor isn't capable of being called recursively, and even if it were, its need to create a transaction block interferes unpleasantly with the desired behavior of \gexec after a failure of a generated query (i.e., that it can continue). Therefore, disable use of ExecQueryUsingCursor when doing the master \gexec query. We can still apply it to individual generated queries, however, and there might be some value in doing so. While testing this feature's interaction with single-step mode, I (tgl) was led to conclude that SendQuery needs to recognize SIGINT (cancel_pressed) as a negative response to the single-step prompt. Perhaps that's a back-patchable bug fix, but for now I just included it here. Corey Huinker, reviewed by Jim Nasby, Daniel Vérité, and myself
This is pretty cool.
It happens, to me, pretty often, that I need to run some query against many tables, or with lots of similar parameters.
For example, let's do a simple test case:
$ seq 1 9 | sed 's/.*/create table t& (id serial primary key, payload text); insert into t& (payload) values ($$whatever$$);/' | psql
This did generate 9 tables, named t1, t2, …, t9, each with the same structure, and single row:
$ SELECT * FROM t1; id | payload ----+---------- 1 | whatever (1 ROW)
Now, let's assume we want to update current row, to have payload of ‘WHATEVER' (uppercase), and insert one more row, with value of ‘PostgreSQL'.
Normally, I would have to write 18 separate queries to do it in all tables, but now, it gets simpler.
First, I will need to generate the update and insert queries. To do it, I'll use pg_class table, as source of table names:
$ SELECT relname FROM pg_class WHERE relkind = 'r' AND relname ~ '^t[0-9]$'; relname --------- t6 t1 t2 t3 t4 t5 t7 t8 t9 (9 ROWS)
Now, having this, I can make the queries I need:
$ SELECT format('UPDATE %I SET payload = upper(payload)', relname) AS UPDATE, format('INSERT INTO %I (payload) values (%L)', relname, 'PostgreSQL') AS INSERT FROM pg_class WHERE relkind = 'r' AND relname ~ '^t[0-9]$'; UPDATE | INSERT ----------------------------------------+------------------------------------------------ UPDATE t6 SET payload = UPPER(payload) | INSERT INTO t6 (payload) VALUES ('PostgreSQL') UPDATE t1 SET payload = UPPER(payload) | INSERT INTO t1 (payload) VALUES ('PostgreSQL') UPDATE t2 SET payload = UPPER(payload) | INSERT INTO t2 (payload) VALUES ('PostgreSQL') UPDATE t3 SET payload = UPPER(payload) | INSERT INTO t3 (payload) VALUES ('PostgreSQL') UPDATE t4 SET payload = UPPER(payload) | INSERT INTO t4 (payload) VALUES ('PostgreSQL') UPDATE t5 SET payload = UPPER(payload) | INSERT INTO t5 (payload) VALUES ('PostgreSQL') UPDATE t7 SET payload = UPPER(payload) | INSERT INTO t7 (payload) VALUES ('PostgreSQL') UPDATE t8 SET payload = UPPER(payload) | INSERT INTO t8 (payload) VALUES ('PostgreSQL') UPDATE t9 SET payload = UPPER(payload) | INSERT INTO t9 (payload) VALUES ('PostgreSQL') (9 ROWS)
Nice. And now, I can run the same query, but instead of ; I will end it with \gexec:
$ SELECT format('UPDATE %I SET payload = upper(payload)', relname) AS UPDATE, format('INSERT INTO %I (payload) values (%L)', relname, 'PostgreSQL') AS INSERT FROM pg_class WHERE relkind = 'r' AND relname ~ '^t[0-9]$' \gexec UPDATE 1 INSERT 0 1 UPDATE 1 INSERT 0 1 UPDATE 1 INSERT 0 1 UPDATE 1 INSERT 0 1 UPDATE 1 INSERT 0 1 UPDATE 1 INSERT 0 1 UPDATE 1 INSERT 0 1 UPDATE 1 INSERT 0 1 UPDATE 1 INSERT 0 1
And now, each of these tables looks like:
$ SELECT * FROM t5; id | payload ----+------------ 1 | WHATEVER 2 | PostgreSQL (2 ROWS)
This might not be something you'll be using every day, but I find it very interesting, and helpful.
And how about doing selects from there?
$ SELECT format('select * FROM %I', relname) FROM pg_class WHERE relkind = 'r' AND relname ~ '^t[0-9]$' LIMIT 2\gexec id | payload ----+------------ 1 | WHATEVER 2 | PostgreSQL (2 ROWS) id | payload ----+------------ 1 | WHATEVER 2 | PostgreSQL (2 ROWS)
Nice. Really nice. We could do something similar with DO but this seems simpler to use in many cases. Thanks a lot.
Glad to see that you’ve already found uses for it!
Yes, it can be done in a DO block, but if something fails, you’re left to wonder which statement failed, or pepper your DO block with lots of RAISE NOTICE statements.
With \gexec, I can just “\set ECHO queries” and now the statements that were ran are there for all to see (and log). I can also monitor progress that way.