In SQL you can generate your own queries, which comes in handy when you have to operate with all the tables from your database.
All in all, you can use simply a concatenation operand and a [subselect][article] to get your results.
For example, if we have the tables from the database
tickit and schema
dragon in the following query:
Then we might be interested in granting select to one user on all the tables from one schema.
We cannot do that directly, but we can:
This will generate all the queries we need. After this it’s just a matter of executing them.
If you are not on Redshift, you can convert this to a prepared query using directly the into command, and then execute the query automatically. On Redshift you need a bit of copy and paste.
Create query generators always instead of changing small things one by one. You'll be less error prone, and it's much more satisfying.
Have you generated interesting queries? How you generate them?