Re: Naive schema questions

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(at)Yahoo(dot)com #