Thread: qualified names

Hello,
If I set search_path to one of the schemas, I can use psql without
writing the qualified names.
In my application instead the server returns error if I dont use select
* from myschema.mytable.
Is this fixable?

thanks...

We can achieve in two ways for that:

a. Make "Jforum'' << new schema name>> the default schema for a session

b. Make "Jforum" <<new schema name>> the default schema permanently

a) That can be done by session level  using the following steps:

First, check the search_path variable by running:

show search_path;

sample output: "$user", public, sys, dbo

Then add the schema by running:

SET search_path to "$user", jforum, public, sys, dbo (Where jforum is the new schema)

Now if you create any new tables they will be part of the 'jforum' schema for a specific session in “psql” until you do a \q and quit out from the “psql”. You have to do it again if you login again from “psql” as it is for one session only.

b) That can be done permanently  using the following steps

1. Stop your database server

2. Open the postgresql.conf file in your 'data' folder and then search for 'search_path' variable. By default the value for 'search_path variable be like

search_path = '$user,pulic,sys,dbo'

Please change it in the following way for your schema Jforum:

search_path = '$user,jforum,pulic,sys,dbo'

3. Now after the change is done, save and close the postgresql.conf file.

4. Start the database server.

Now when you do:

create table test1(a number);

That will be made automatically in the 'jforum' schema.

Thanks & Regards

Raghu Ram

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

On Sun, 2009-09-13 at 19:38 +0200, manaus wrote:
> Hello,
> If I set search_path to one of the schemas, I can use psql without
> writing the qualified names.
> In my application instead the server returns error if I dont use select
> * from myschema.mytable.
> Is this fixable?
>
> thanks...
>
>

There are several ways to deal with this.

1.  To solve the problem for the current instance, do:
SET search_path TO schema1, schema2, ... ;
from you application as required.

2.  For a permanent solution for that user only, do:
ALTER USER <username> SET search_path TO schema1, schema2, ... ;
for the username that your application connects as.  The new settings
will take effect on the next login.  There is no need to stop the
database server to make these changes.

HTH,
Tim Bowden