Set search_path for session

On Fri, Apr 17, 2009 at 10:47 AM, Mary Sipple <msipple@iii.com> wrote:

We have a script wrapper for psql which reads a configuration file and
constructs a psql command to connect to the configured instance of postgres.
We would like to include a search_path setting in the configuraration file
and have the wrapper use that to set the search_path for the psql session. I
tried using the -v flag (-vsearch_path=xxxx) with the psql command but
that did not work:

psql -h$SOCKET -U$USER -p$PORT -vsearch_path=$SCHEMAS $DBNAME

The search_path remains at the default setting:

iii=> show search_path;

search_path

----------------

"$user",public

(1 row)

Can anyone tell me how I can make this happen short of changing thesearch_path setting in postgresql.conf & restarting postgres?

In your users's home directory, place a '.psqlrc' file with the command 'set
search_path=<blah>'

http://www.postgresql.org/docs/8.3/static/app-psql.html

--Scott

On Fri, Apr 17, 2009 at 10:47 AM, Mary Sipple <msipple@iii.com> wrote:

We have a script wrapper for psql which reads a configuration file and
constructs a psql command to connect to the configured instance of postgres.
We would like to include a search_path setting in the configuraration file
and have the wrapper use that to set the search_path for the psql session. I
tried using the -v flag (-vsearch_path=xxxx) with the psql command but
that did not work:

psql -h$SOCKET -U$USER -p$PORT -vsearch_path=$SCHEMAS $DBNAME

The search_path remains at the default setting:

iii=> show search_path;

search_path

----------------

"$user",public

(1 row)

Can anyone tell me how I can make this happen short of changing thesearch_path setting in postgresql.conf & restarting postgres?

In your users's home directory, place a '.psqlrc' file with the command 'set
search_path=<blah>'

http://www.postgresql.org/docs/8.3/static/app-psql.html

--Scott

Mary Sipple wrote:

We have a script wrapper for psql which reads a configuration file and
constructs a psql command to connect to the configured instance of postgres.
We would like to include a search_path setting in the configuraration file
and have the wrapper use that to set the search_path for the psql session. I
tried using the -v flag (-vsearch_path=xxxx) with the psql command but that
did not work:

psql -h$SOCKET -U$USER -p$PORT -vsearch_path=$SCHEMAS $DBNAME

This should work:

export PGOPTIONS="-c search_path=$SCHEMAS"
psql -h$SOCKET -U$USER -p$PORT $DBNAME

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Fri, Apr 17, 2009 at 8:47 AM, Mary Sipple <msipple@iii.com> wrote:

We have a script wrapper for psql which reads a configuration file and
constructs a psql command to connect to the configured instance of postgres.
We would like to include a search_path setting in the configuraration file
and have the wrapper use that to set the search_path for the psql session. I
tried using the -v flag (-vsearch_path=xxxx) with the psql command but that
did not work:

Can anyone tell me how I can make this happen short of changing the
search_path setting in postgresql.conf & restarting postgres?

You can also alter user or database as well.

alter user abc set search_path='xyz','public';

Thanks everyone!

A follow up question: Is there any way to set the search_path to all
existing schemas? I'm looking for some kind of wild card method that will
automatically pick up every schema.

Thanks, Mary