And here I am again, with another PostgreSQL tip.
Recently, I had a need of running psql interactively but executing some script first. Of course, this can be done from psql itself by saying \i some-script.psql, but let’s assume that I’d prefer for that to happen automatically, so that instead of typing psql at the shell prompt and then \i some-script.psql at the psql prompt I could just say psql-some-script.sh at the shell prompt and be done. The important thing here is that I want to run some-script.psql first and go to the psql prompt next.
(Note: for simplicity, I obviously omit things like command-line parameters to psql or file paths.)
A naive approach would be to define psql-some-script.sh to say something like psql -f some-script.psql -f - to first execute all the commands from some-script.psql and then take the commands from stdin. This has a significant drawback: it disables the Readline library, which I find extremely useful when interacting with psql interactively. (One of the things Readline gives us is command history.)
The hope is not all lost, however. Here is a simple trick to have this cake and eat it, too. Let’s write the some-script.psql file in the following way:
\i ~/.psqlrc select 2+2;
(of course, in reality, the second line would be the things I need to run when starting psql), and then it is enough to have our psql-some-script.sh script run this:
PSQLRC=some-script.psql psql
This way psql will start with executing ./psqlrc (as it should do), then our script (represented by the 2+2 calculation above) and finally give us the prompt in its full Readline glory.
By the way, if you’d also like to have some cleanup, e.g., drop some temporary table after everything is done in the psql REPL, you can put it after the psql invocation in psql-some-script.sh, using a separate psql invocation like this:
psql -c 'drop table some-table;'
(And yes, I know about temporary tables, but they are not well-suited to every use-case. For instance, if I switch to another database within the same psql session, it means creating a new session from PostgreSQL’s perspective, so temporary tables do not survive that.)
Have fun querying! 