Schema + search path problem

From: "John Sidney-Woollett" <johnsw(at)wardbrook(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Schema + search path problem
Date: 2003-12-11 16:33:49
Message-ID: 1125.192.168.0.64.1071160429.squirrel@mercury.wardbrook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm slightly confused by schemas and search paths...

I'm using Postgres 7.4, and have broken my original test database into
various schemas now. Using google I found a reference to a 7.3 document
that provided quite a lot of information on schemas (in the section on SQL
syntax).

It appears that by default, the search path is a schema the same as your
login name, and then public.

I have created the schema in a database as the postgres user, and also
created all that schema's objects (as the postgres user).

Later I will connect to the schema via another user (say 'FRED') (who will
have appropriate access rights to the objects in the schema).

Question 1
==========
Is there a way to set the default search path for the user 'FRED'
permanently? Or do you have to reset the search path (and/or default
schema) for each new session?

Question 2
==========
If I am the postgres user and creating a function (for example) in a given
schema, do I have to qualify ALL the objects referred to in the function
by the schema prefix (if I don't want them in the public schema)?

And if I use the "SET search_path TO schema" command, am I still required
to be explicit about the schema name for objects referred to in a newly
created function?

Thanks for shedding any light on the above.

John Sidney-Woollett

ps Is the 7.4 documentation less detailed on schemas than the 7.3 docs? Or
have I missed a section somewhere?

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marc G. Fournier 2003-12-11 17:02:36 Re: add column sillyness
Previous Message Tom Lane 2003-12-11 16:26:14 Re: Strange permission problem regarding pg_settings