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?
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.
Yes, you can use the startup file psqlrc for that. Per documentation:
psqlrcand~/.psqlrcUnless it is passed an
-Xor-coption, 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.
There are 2 workaround approaches.
psql \cd command. cd your $HOME folder.
sql>
\cd
\i git/my_project/src/abc.sql
OS soft link,
$> ln -s ~/git/my_project ~/work/project/my_project
psql
sql> \i ~/work/project/my_project/abc.sql
