PSQL POSTGRESQL POWERUSER PRACTICE: GEXEC 

Preamble

Automating routine tasks is becoming more and more important for PostgreSQL power users, and gexec can help. This article will demonstrate how to use the gexec command and the || operator to cut down on repetitive tasks in your workflow.
Psql is the name of the CLI client that comes along with PostgreSQL. Like many CLI clients, it is frequently disregarded in favor of something with a GUI or only utilized for the most elementary tasks, with more complicated operations being performed elsewhere. Psql, on the other hand, is a very powerful tool with a wealth of helpful capabilities.

The requirement to execute the same command with various arguments is a frequent occurrence. Users frequently choose to repeatedly type the same command in text editors or to write it once in a text editor, copy it, paste it, then change it to accept various arguments.

Automating such processes can occasionally be advantageous, not just in terms of time savings but also in terms of preventing mistakes brought on by typos or copy-paste. PostgreSQL may generate commands with the results of queries as parameters by taking the results of queries and adding text.
Because of this, the || operator can be used to add or take away content from any query result.

Exercise 1: using the || operator

Consider a scenario in which a new user wants access to specific tables inside a schema, such as all the tables that match a specific prefix.
Now, we could either do this by hand or ask the database to do the boring work for us.

1. Let’s get the pertinent tables, whose names begin with pgbench.

postgres=# SELECT tablename FROM pg_tables WHERE tablename~'^pgbench'; tablename ------------------ pgbench_accounts pgbench_branches pgbench_history pgbench_tellers (4 rows)

2. Let’s use || to prepend and add command fragments to make a command that works and takes the tablename as an argument. ​

postgres=# SELECT 'GRANT SELECT ON TABLE ' || tablename || ' TO someuser;' FROM pg_tables WHERE tablename~'^pgbench'; ?column? ----------------------------------------------------- GRANT SELECT ON TABLE pgbench_accounts TO someuser; GRANT SELECT ON TABLE pgbench_branches TO someuser; GRANT SELECT ON TABLE pgbench_history TO someuser; GRANT SELECT ON TABLE pgbench_tellers TO someuser; (4 rows)

Because the tablename itself lacks the spaces required for argument separation, the strings have additional spaces at the beginning or end. To enable immediate execution of these commands, the semicolon (;) was also added.

Please remember that while using || to concatenate things is convenient, it is not recommended because it is susceptible to SQL injection attacks, as a helpful commenter described below:

Do NOT blindly concatenate table names with queries. Use quote_ident(), or format() with %I, instead. These apply correct escaping as necessary.

A safer approach to achieve the same results would be something like this:

postgres=# SELECT format('GRANT SELECT ON TABLE %I TO someuser;', tablename) FROM pg_tables WHERE tablename~'^pgbench'; format ----------------------------------------------------- GRANT SELECT ON TABLE pgbench_accounts TO someuser; GRANT SELECT ON TABLE pgbench_branches TO someuser; GRANT SELECT ON TABLE pgbench_history TO someuser; GRANT SELECT ON TABLE pgbench_tellers TO someuser; (4 rows)

Now, one may copy these commands, paste them into the prompt, and execute them. 

Even better, I’ve seen folks take these lines, put them in a file, and then tell psql to run every command in the file.

But happily, there is a much simpler approach.

\gexec

To quickly acquire information about the database, schemas, tables, rights, and much more, psql offers a number of shortcuts and tools. When used with gexec, the psql shell’s ability to interact with input and output buffers can be used to make psql run each command from the output buffer. 

Exercise 2: calling \gexec

​By using the query to make the necessary commands, we can use gexec to run each line from the previous output. ​

postgres=# SELECT 'GRANT SELECT ON TABLE ' || tablename || ' TO someuser;' FROM pg_tables WHERE tablename~'^pgbench'; ?column? ----------------------------------------------------- GRANT SELECT ON TABLE pgbench_accounts TO someuser; GRANT SELECT ON TABLE pgbench_branches TO someuser; GRANT SELECT ON TABLE pgbench_history TO someuser; GRANT SELECT ON TABLE pgbench_tellers TO someuser; (4 rows) postgres=# \gexec GRANT GRANT GRANT GRANT

Exercise 3: a cross join with \gexec

You may always add more || to add extra command fragments around a query’s results if you wish to do anything that requires more parameters.
Let’s say you also need to give access to insert into, update, and delete from certain tables.

For each of the table names, a straightforward cross join provides the appropriate action (created as a relation using the VALUES constructor).

postgres=# SELECT action, tablename FROM pg_tables CROSS JOIN (VALUES ('INSERT'),('UPDATE'),('DELETE')) AS t(action) WHERE tablename~'^pgbench'; action | tablename --------+------------------ INSERT | pgbench_accounts UPDATE | pgbench_accounts DELETE | pgbench_accounts INSERT | pgbench_branches UPDATE | pgbench_branches DELETE | pgbench_branches INSERT | pgbench_history UPDATE | pgbench_history DELETE | pgbench_history INSERT | pgbench_tellers UPDATE | pgbench_tellers DELETE | pgbench_tellers (12 rows)

Note that we explicitly assign the action column name using AS t(action) to the table generated using VALUES.

postgres=# SELECT 'GRANT ' || action || ' ON TABLE ' || tablename || ' TO someuser;' FROM pg_tables CROSS JOIN (VALUES ('INSERT'),('UPDATE'),('DELETE')) AS t(action) WHERE tablename~'^pgbench'; ?column? ----------------------------------------------------- GRANT INSERT ON TABLE pgbench_accounts TO someuser; GRANT UPDATE ON TABLE pgbench_accounts TO someuser; GRANT DELETE ON TABLE pgbench_accounts TO someuser; GRANT INSERT ON TABLE pgbench_branches TO someuser; GRANT UPDATE ON TABLE pgbench_branches TO someuser; GRANT DELETE ON TABLE pgbench_branches TO someuser; GRANT INSERT ON TABLE pgbench_history TO someuser; GRANT UPDATE ON TABLE pgbench_history TO someuser; GRANT DELETE ON TABLE pgbench_history TO someuser; GRANT INSERT ON TABLE pgbench_tellers TO someuser; GRANT UPDATE ON TABLE pgbench_tellers TO someuser; GRANT DELETE ON TABLE pgbench_tellers TO someuser; (12 rows)

This output can then again be executed using \gexec.

postgres=# \gexec GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT

Exercise 4: adding quotes

​Depending on the situation, additional quotations may need to be added to the output, such as when table names have capitalization or spaces. In some cases, you can change the strings that are prepended and appended to arguments by adding matching double quotes (“). ​

postgres=# SELECT 'GRANT SELECT ON TABLE "' || tablename || '" TO someuser;' FROM pg_tables WHERE schemaname='public'; ?column? ----------------------------------------------------- GRANT SELECT ON TABLE "with spaces" TO someuser; GRANT SELECT ON TABLE "Capitalization" TO someuser; GRANT SELECT ON TABLE "capitalization" TO someuser; (3 rows) postgres=# \gexec GRANT GRANT GRANT

Why not move on to the next stage now that you understand how to utilize GExEC? To see it utilized in another real-world example, visit our blog post on column ordering in PostgreSQL.​

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​