Skip site navigation (1) Skip section navigation (2)

Re: Naive schema questions

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
Cc: Postgresql General List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Naive schema questions
Date: 2004-05-27 12:18:14
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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.


# 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 #

In response to


pgsql-general by date

Next:From: Nick BarrDate: 2004-05-27 12:32:14
Subject: Re: Naive schema questions
Previous:From: Peter EisentrautDate: 2004-05-27 12:10:35
Subject: Re: Naive schema questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group