gexec in psql: PostgreSQL poweruser practice

07.2022 / Category: / Tags: | |

For PostgreSQL powerusers, automating repeated steps is becoming more and more necessary, and gexec can help. This blog will show you how to use the || operator and the gexec command to avoid unnecessary repetition in your workflow.

The CLI client that ships with PostgreSQL is called psql. Like many CLI clients, it is often overlooked and replaced with something with a GUI, or it is only used for the most basic tasks, while more complex operations are carried out elsewhere. However, psql is a very capable tool with lots of useful features.

One common pattern is the need to run the same command with different arguments. Often, users simply rewrite the command over and over, or sometimes they may opt to use a text editor to write the command once, then copy and paste and edit it to accommodate different arguments.

Sometimes it can be useful to automate such steps, not only in the interest of saving time, but also in the interest of avoiding errors due to typos or copy-pasting. PostgreSQL can take the results of queries and add text to create commands with those results as arguments.
For this purpose, we can prepend or append text to any query result using the || operator.

Exercise 1: using the || operator

Let's assume a new user needs access to some tables in a schema, e.g. all those tables that match a certain prefix.
Now, we could do this manually, or ask the database to automate the boring stuff.

1. Let's retrieve the relevant tables with names starting with pgbench

2. Let's use || to prepend and append command fragments to create a valid command with the tablename as a parameter.

Note that the strings end or begin with additional spaces, as the tablename itself does not contain the necessary spaces for argument separation. The semicolon ; was also added so these commands could be run straight away.

Please keep in mind that, while it is convenient to use || to concatenate things, it is not considered good practice, as it can be vulnerable to SQL injection attacks, as a helpful commenter detailed 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:

Now, these commands could be copied and then pasted straight into the prompt.
I've even seen people take such lines, store them into a file and then have psql execute all commands from the file.

But thankfully, a much easier way exists.


In psql, there are many shortcuts and helpers to quickly gather info about the database, schemas, tables, privileges and much more.
The psql shell allows for working on the input and output buffers, and this can be used together with gexec to have psql execute each command from the output buffer.

Exercise 2: calling gexec

Reusing the query to generate the necessary commands, we can call gexec to execute each line from the previous output.

Exercise 3: a cross join with gexec

Assuming that you want to do something involving more arguments, you can always add more || to add more command fragments around the results from a query.
Suppose you need to grant privileges to insert, update, and delete from those tables as well.

A simple cross join gives us the desired action (constructed as a relation using the VALUES constructor) for each of the table names.

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

This output can then again be executed using gexec.

Exercise 4: adding quotes

Depending on the circumstances, it may be required to add additional quotes to the output, for example when table names contain capitalization or spaces. In such cases, matching double quotes " can be added to the strings prepended and appended to arguments.

Now that you know how to use gexec, why not take the next step? Take a look at our blog on column order in PostgreSQL to see it used in another practical example.

If you would like to learn more about security in PostgreSQL, see my blog about Transport Layer Security.

Find out about jobs at CYBERTEC PostgreSQL International - we are hiring!

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
Marti R.
Marti R.
1 year ago

Do NOT blindly concatenate table names with queries. Use quote_ident(), or format() with %I, instead. These apply correct escaping as necessary.
Blog articles that teach users to create SQL injection vulnerabilities are actively harmful.

Julian Markwort
Julian Markwort
1 year ago
Reply to  Marti R.

Thanks for the input.
This is supposed to be a simple demonstration, and every DBA should read carefully what they have in their buffer before executing gexec .
I'm not suggesting anybody put this in their automation to be executed blindly, where it could be susceptible to these issues.

I'll add a section to use format(), including a disclaimer.

Corey Huinker
Corey Huinker
1 year ago

You'll want to use the %I.%I construct in a format() and pass in the nspname and relname rather than ever blindly putting double-quotes in your format strings.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram