postgres psql script path

Question

I am making a directory of useful sql scripts to use psql. I would like to be able to use them without having to cd to the directory.

Is it possible to configure psql to search a particular path for invoked scripts? Or do I have to invoke them all with fully qualified names?

Answer 1

You could simply place your scripts in your .psqlrc file:

\set uptime 'select now() - backend_start as uptime from pg_stat_activity where pid = pg_backend_pid();'

and then run the query in psql by prepending it with a colon :uptime

OR

if your scripts are long and you wish to better organize them then you could create your scripts directory ~/psqlrc.d or whatever and then (for example) (assuming a *nix OS):

echo "select now() - backend_start as uptime from pg_stat_activity where pid = pg_backend_pid();" > ~/psqlrc.d/uptime.sql

followed by editing your ~/.psqlrc file to add:

\set uptime '\\i ~/psqlrc.d/uptime.sql'

and once again invoke the script by typing :uptime in psql.

Answer 2

Yes, you can use the startup file psqlrc for that. Per documentation:

psqlrc and ~/.psqlrc

Unless it is passed an -X or -c option, psql attempts to read and execute commands from the system-wide startup file (psqlrc) and then the user's personal startup file (~/.psqlrc), after connecting to the database but before accepting normal commands.

Create the file (if it does not exist yet) and put the psql meta-command \cd in there. Per documentation:

\cd [ directory ]

Changes the current working directory to directory. Without argument, changes to the current user's home directory.

For instance, put this in the personal startup file of your user ~/.psqlrc:

\set QUIET ON
\cd /var/lib/postgres/script/
\set QUIET OFF

\set QUIET ON and \set QUIET ON optionally suppress a message from \cd for every start.