CODEWITHSUNDEEP
postgresqlpsql
Michael Stack
Michael Stack

Reputation: 375

postgres psql script path

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?

Upvotes: 3

Views: 3481

Answers (3)

Charlie 木匠
Charlie 木匠

Reputation: 2390

There are 2 workaround approaches.

  1. psql \cd command. cd your $HOME folder.

    sql>

    \cd

    \i git/my_project/src/abc.sql

  2. OS soft link,

    $> ln -s ~/git/my_project ~/work/project/my_project

    psql

    sql> \i ~/work/project/my_project/abc.sql

Upvotes: 0

gsiems
gsiems

Reputation: 3790

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.

Upvotes: 3

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656666

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.

Upvotes: 2

Related Questions