We use this script as a proxy for psql, the user can run this followed by the schema's they want in the search path on the command line & the PGOPTIONS value sets it up for them...
schema=`echo $@|sed 's/, /,/g'|tr " " ","|sed 's/,,/,/g'`
#echo $schema
export PGOPTIONS="-c search_path=$schema,public,maps"
psql fish
In your case this could perhaps be used by each application to customise the run time environment, so each has it's own PGOPTIONS string, and thus, when Postgres is run, it's own search path.
Brent Wood
Programme leader: Environmental Information Delivery
NIWA
DDI: +64 (4) 3860529
________________________________________
From: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org] on behalf of Scott Marlowe [scott.marlowe@gmail.com]
Sent: Thursday, February 27, 2014 7:32 PM
To: itishree sukla
Cc: Postgres General
Subject: Re: [GENERAL] Multiple Schema in One DB
On Wed, Feb 26, 2014 at 11:24 PM, itishree sukla
<itishree.sukla@gmail.com> wrote:
> Hi all,
>
> I am in a requirement to have multiple schema in one Database.
>
> Schema structure will be same with respect to DB all db objetcs like tables,
> procedure.
>
> However each schema will be accessed by one one application.
>
> Problem is as all DB objects are same, when application is calling to a
> schema we have set search_path='schema name' in our procedure level. Now we
> want to get rid of that.
>
> I know we have do it based on ROLE Level, question is having more than one
> user defined schema is it achievable, i have done a small exercise, seems to
> be it is taking the first schema defined in the set search_path.
>
> As i am in urgent need of it, please help me.
You can alter user to each one have their own search path.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Please consider the environment before printing this email.
NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
