Any DBA will sometimes have to execute some kind of batch DDL / DCL operation, for example:
- grant privileges for all tables in all schemas to a specific user,
- create a spatial index for all GEOMETRY columns that doesn’t have such index,
- add a list of tables to logical replication,
- alter all INTEGER columns to BIGINT,
- … and many more.
If the database structure is simple with only a handful of tables, this can be done manually without much effort. But what if there are dozens of schemas, hundreds of tables and thousands of columns? Possible automation solutions include writing a script in a scripting language of choice (Python, Perl…) or a pl/pgsql procedure. But there’s one more possibility: use one of lesser known commandline psql client superpowers – the \gexec command.
How it works? It takes the query output and executes it as a SQL statement. For example, to grant SELECT privileges for all tables in all schemas to “chinook” user, you will want to execute the following command:
SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || quote_ident(schema_name) || ' TO chinook;' FROM information_schema.schemata; \gexecpsql will output the result – a set of “GRANT SELECT ON ALL TABLES IN SCHEMA …” statements – and execute them one by one. The use of quote_ident function around object name isn’t technically necessary, but helpful – if any name requires double quotes, the database engine will add them automatically.
Personally, I like this method very much because it’s a huge time saver, and doesn’t require any boilerplate code.