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

Naive schema questions

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: Postgresql General List <pgsql-general(at)postgresql(dot)org>
Subject: Naive schema questions
Date: 2004-05-27 11:15:59
Message-ID: 20040527111559.GB7956@campbell-lange.net (view raw or flat)
Thread:
Lists: pgsql-general
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.)

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.

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. 

Thanks for any observations
Rory

-- 
Rory Campbell-Lange 
<rory(at)campbell-lange(dot)net>
<www.campbell-lange.net>

Responses

pgsql-general by date

Next:From: Peter EisentrautDate: 2004-05-27 12:10:35
Subject: Re: Naive schema questions
Previous:From: Richard HuxtonDate: 2004-05-27 07:55:22
Subject: Re: Connection log entries - random port number recorded

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