Sunday, June 1, 2008

Re: [NOVICE] Running .psqlrc from a custom location

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
>
>

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

No comments: