神刀安全网

Hubert 'depesz' Lubaczewski: Waiting for 9.6 – Add a /gexec command to psql for evaluation …

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.

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Hubert 'depesz' Lubaczewski: Waiting for 9.6 – Add a /gexec command to psql for evaluation …

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址