Re: Naive schema questions - Mailing list pgsql-general

On 5/27/2004 7:15 AM, Rory Campbell-Lange wrote:

> I have a number of web applications which have a large amount of their
> logic written in plpgsql. For each particular application I have a
> separate instance of a database. If I need to update functions I have to
> connect to each database and then run \i fn_function_update.sql.
>
> I imagined schemas might allow me to globally update functions across a
> database hosting many schemas with the same structure. In this scenario
> my webapp would always connect to the same database, but address
> different schemas, so that
>
>     mysolution.schema_A.people would be addressed for site 'A'
>     and
>     mysolution.schema_B.people would be addressed for site 'B'
>
> (I'm assuming here that I can set the context of the schema at
> connection by a plpgsql line that sets the current search path.)

That is so.

>
> However
>
>      Schemas also contain other kinds of named objects, including data
>      types, functions, and operators.
>                                       (html reference: ddl-schemas.html)
>
> seems to suggest that the functions are schema specific.

It is even better. The property that set's your "schema context" is
called search_path. This contains a list of schema names. For an
unqualified (schema name not explicitly given) object, be that a table,
sequence, view, function or whatever, the system looks in all those
schemas in that particular order and uses the first found.

With that, you can have your common or shared objects in a central
schema "schema_common", and everything that's application specific in
"schema_A", "schema_B". The connection just has to set the search_path
at the beginning with

     set search_path = schema_A, schema_common;

and done.

>
> I suppose I'm trying to think of how I might implement the second point
> in this list (also from dd-schemas.html):
>
>     There are several reasons why one might want to use schemas:
>     - To allow many users to use one database without interfering with
>       each other.
>     - To organize database objects into logical groups to make them more
>       manageable.
>     - Third-party applications can be put into separate schemas so they
>       cannot collide with the names of other objects.

Yes, yes and yes. Plus the ability for you to do cross database joins
for global analyzing for example.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #