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@campbell-lange.net>
<www.campbell-lange.net>