CODEWITHSUNDEEP
postgresqlshellpsql
user3112401
user3112401

Reputation: 373

How do I execute a query automatically in PostgreSQL when connecting via shell?

I want to be able to execute a statement automatically when I connect to Postgres with psql and then remain connected so I can type in further commands.

Currently, every time I connect, the first thing I do is type:

SET search_path = 'something';

Or maybe I would want to do something else like:

SELECT COUNT(*) FROM sometable;

I know there is a -c argument to psql that will execute a command and then exit. I'm looking for a way I can execute a command upon connecting and then remain in the client.

(Note: I prefer not to alter the database, schema or role to make the search_path permanent, as all of the solutions I have found seem to dictate. I want to set it every time I connect. But again, this question could apply any SQL statement.)

I have tried this:

echo "SET search_path TO 'mything'" | psql

but that behaves the same way as:

psql -c "SET search_path TO 'mything'"

Is what I'm asking for doable?

Upvotes: 3

Views: 2586

Answers (1)

Grant Gainey
Grant Gainey

Reputation: 124

psql will look for, and execute any commands found in, a couple of places every time it starts up. One is the system-wide psqlrc file, and one is in the home-directory of the login that's running psql, ~/.psqlrc.

So, you could add the SET command that you always want to be run, to your .psqlrc file, and it'll get executed every time you start up. See the example below: 

~ $ cat ~/.psqlrc
SET search_path='mything';
~ $ psql
SET search_path='mything';
SET
psql (8.4.20, server 9.2.10)
WARNING: psql version 8.4, server version 9.2.
     Some psql features might not work.
Type "help" for help.
rhnschema=# show search_path;
search_path 
-------------
mything
(1 row)
rhnschema=#

Upvotes: 6

Related Questions