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