Thread: Running .psqlrc from a custom location
Hi folks, I use a hack to make psql automatically set search_path to the relevant schema for the dump directory I am currently working in. I achieved this by running a wrapper script that changed the HOME environment variable, setting a variable on the command line, and having the .psqlrc in the custom location use that variable to set the search path. I just upgraded to 8.1 from 7.4 and my hack stopped working - apparently psql does not use the $HOME variable any more. My scripts are included below. I do not wish to run these commands every time I use psql, only when I run it from the wrapper script. As far as I am aware there is no way of setting search_path/schema from the command line. If somebody could suggest an alternative way of causing psql to run a .psqlrc file from an arbitrary location, or even better, a way of manually specifying search_path when starting psql, I will be eternally indebted. Thanks, Netzach --- BEGIN specialdirectory/spsql.sh #!/bin/bash [ -f dbname.txt ] && PGDATABASE=`cat dbname.txt` [ -f schemaname.txt ] && SCHEMANAME=`(echo '-v schema='\`cat schemaname.txt\`)` [ -f encoding.txt ] && PGENCODING=`(echo '-v encoding='"\`cat encoding.txt\`")` [ -f user.txt ] && PGUSER=`(echo -U; cat user.txt; echo -h localhost)` PGHOST=`[ -f host.txt ] && (echo '-h'; cat host.txt) || \ [ -f user.txt ] && echo ' -h localhost'` HOME=`dirname \`readlink $0 || echo $0\`` \ psql $SCHEMANAME $PGENCODING $PGHOST $PGUSER $PGDATABASE --- END spsql.sh --- BEGIN specialdirectory/.psqlrc SET search_path TO :schema; SET client_encoding TO :encoding; --- END .psqlrc
Netzach <psql-novice@netzach.co.il> writes: > I use a hack to make psql automatically set search_path to the relevant > schema for the dump directory I am currently working in. I achieved this > by running a wrapper script that changed the HOME environment variable, > setting a variable on the command line, and having the .psqlrc in the > custom location use that variable to set the search path. I just > upgraded to 8.1 from 7.4 and my hack stopped working - apparently psql > does not use the $HOME variable any more. No, it doesn't, and changing $HOME is probably a pretty bad idea in any case. > My scripts are included below. I do not wish to run these commands every > time I use psql, only when I run it from the wrapper script. As far as I > am aware there is no way of setting search_path/schema from the command > line. Try something like export PGOPTIONS="--search-path=foo,bar" before starting psql. regards, tom lane
I believe you can create a user with the same name as the search_path schema, so that when you login to the database with that user, and run commands, the search path defaults to the same name as the user. I have a system with several schemas and I use a specific USER for each schema that has the same name as the schema. ie. schema "demo", user "demo". Login as "demo" and the schema should default to "demo". Now this may also require that you set the search path parameter in your configuration file. It is here that you can tell the system to look for USER, then PUBLIC schemas (in that order) when someone logs in so the system knows which schema to point the newly logged in user to. Keep in mind, I am a novice as well, but this is what I did to get it working. Derrick Netzach wrote: > Hi folks, > > I use a hack to make psql automatically set search_path to the relevant > schema for the dump directory I am currently working in. I achieved this > by running a wrapper script that changed the HOME environment variable, > setting a variable on the command line, and having the .psqlrc in the > custom location use that variable to set the search path. I just > upgraded to 8.1 from 7.4 and my hack stopped working - apparently psql > does not use the $HOME variable any more. > > My scripts are included below. I do not wish to run these commands every > time I use psql, only when I run it from the wrapper script. As far as I > am aware there is no way of setting search_path/schema from the command > line. > > If somebody could suggest an alternative way of causing psql to run a > .psqlrc file from an arbitrary location, or even better, a way of > manually specifying search_path when starting psql, I will be eternally > indebted. > > Thanks, > > Netzach > > --- BEGIN specialdirectory/spsql.sh > #!/bin/bash > > [ -f dbname.txt ] && PGDATABASE=`cat dbname.txt` > [ -f schemaname.txt ] && SCHEMANAME=`(echo '-v schema='\`cat schemaname.txt\`)` > [ -f encoding.txt ] && PGENCODING=`(echo '-v encoding='"\`cat encoding.txt\`")` > [ -f user.txt ] && PGUSER=`(echo -U; cat user.txt; echo -h localhost)` > PGHOST=`[ -f host.txt ] && (echo '-h'; cat host.txt) || \ > [ -f user.txt ] && echo ' -h localhost'` > > HOME=`dirname \`readlink $0 || echo $0\`` \ > psql $SCHEMANAME $PGENCODING $PGHOST $PGUSER $PGDATABASE > --- END spsql.sh > > --- BEGIN specialdirectory/.psqlrc > SET search_path TO :schema; > SET client_encoding TO :encoding; > --- END .psqlrc > >