Re: Naive schema questions

Fabulous stuff! I am so delighted I chose Postgresql a couple of year
ago. Thank you for the valuable insights. A comment or two below:

On 27/05/04, Peter Eisentraut (peter_e@gmx.net) wrote:
> Am Donnerstag, 27. Mai 2004 13:15 schrieb Rory Campbell-Lange:
> > I imagined schemas might allow me to globally update functions across a
> > database hosting many schemas with the same structure.

> Put your data tables in separate schemas, put the functions in yet
> another schema, and then when you connect set the schema search path
> to "dataschema, functionschema" (or maybe vice versa).

On 27/05/04, Nick Barr (nicky@chuckie.co.uk) wrote:
> > Put your data tables in separate schemas, put the functions in yet
> > another schema, and then when you connect set the schema search path
> > to "dataschema, functionschema" (or maybe vice versa).

> Or when you make the calls in the web app use the following:
>
> SELECT function_schema.function1(arg1, arg2);
> instead of just:
> SELECT function1(arg1, arg2);
> But like Peter said have a schema per client/"instance" of your database.

Is it ok to use the public schema for the functions? It means it is that
much easier to reload the functions as one wouldn't need to specify the
search_path.

On 27/05/04, Jan Wieck (JanWieck@Yahoo.com) wrote:
...
> 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;

This is brillliant. I didn't note this in the documentation.

> >I suppose I'm trying to think of how I might implement the second point
> >in this list (also from dd-schemas.html):
...
> >    - To organize database objects into logical groups to make them more
> >      manageable.
...
> Yes, yes and yes. Plus the ability for you to do cross database joins
> for global analyzing for example.

Just a question on this, Jan. Would one expect UNIONS for this sort of
work?

I just did this which is useful anyway:
schematest=> SELECT
            (select count(id) from b.messages)
            +
            (select count(id) from a.messages);
 ?column?
 ----------
         5
         (1 row)

I see the horizons expanding! Common data (I often have an 'info' table)
can be shared between schemas. I think my search_patch might go:

    this_schema, info_schema, public_schema

Thanks very much for the information.

Kind regards,
Rory
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>