Say you want to run a query a specific number of times and then stop… trivial right? Not as much as I first thought. Below is a recreation of how I thought this was supposed to work (I’ve subbed in select now(); for my actual query, as it’s a bit more illustrative.)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | |
My first attempt, without any thought really, was that the right syntax for this was \g 10. When it didn’t work, I stepped through the idea, first verifying my select was good, then verifying \g worked as expected, and then trying the \g 10 on it’s own. When that didn’t work, I double checked the docs and then hit irc to ask if anyone remember that syntax working… of course the answer was no.
If you are wondering, I think I was conflating \g (which among other things causes psql to either execute the query on the line or re-run the previous query) with \w which also re-runs the previous query, but takes an argument equal to the number of seconds to delay between each run; so \w 10 will “watch” your query every 10 seconds for eternity.
So, that didn’t work, so how to do this easily? Well, the best suggestion from irc was to wrap the query in a quick shell loop, which I admit is a simpel enough way to solve this, but to be honest I wanted an sql level way to handle this. The most obvious solution there was to wrap the query into a DO script and just loop through 10 times, but even that felt more cumbersome than it should have been, not to mention that would have put all 10 queries in the same transaction context, which probably didn’t matter, but wasn’t something I wanted to think about.
And that’s when \gexec popped into my head. Ok, it doesn’t hurt I had just read the docs; but postgres has such a large feature set that even us old timers forget all the things it can do. For the record, the docs describe \gexec as so:
Sends the current query buffer to the server, then treats each column of each row of the query’s output (if any) as a SQL statement to be executed.
Ok, there’s actually more, so go check out the docs, but the main part here was if I could just generate the query enough times, then I could use \gexec to run it for me. Of course anytime you’re dealing with loops at the SQL level, generate_series() should come to mind, and so marrying the two, you get:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | |
By using generate_series() I can generate exactly how many copies of the statement I want (and dynamically substituate in info as needed) and each query will run as it’s own statement, all without leaving psql. It’s the little things eh?
Note: If you like this post and think \gexec is going to be a usefel addition for your tool box, you may be equally excited to know that just this week Postgres released a round of security fixes which includes a fix for a nasty exploit involving \gexec. Yeah, that sucks, but maybe now you’ll get some value out of the thing you have to patch. Take what you can get, it’s 2020.
Note redux: Thats what I get for not double checking. The security fix was for \gset, not \gexec. Apologies for any confusion and/or if you accidentally upgraded your database because of my post.