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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

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