Re: Triggers and Multiple Schemas.

Hi,
Yes my db is indeed like

dbname.myschema1.sometable
dbname.myschema2.sometable
dbname.myschema2.sometable2
dbname.myschema2.sometable3

Physically all data is in one db .. however each client has there own
schema (or virtual db). Each client schema has identical structure. And
a number of tables have triggers that are identical in each schema. My
problem at the moment is that I also define the trigger functions in
each schema. This is a complete nightmare to maintain in our case since
we will be very rapidly introducing upto about 400 identical schemas
into a single db.

The reason we are doing this is to have resource and connection pooling
(therefore scalability) for many of our clients who run our system.

So how can I get the schema name of the calling table trigger and use it
in the form of set Search_path at the beginning of the function ?

Regards

Paul Newman

-----Original Message-----
From: Louis Gonzales [mailto:louis(dot)gonzales(at)linuxlouis(dot)net]
Sent: 08 March 2006 20:43
To: Scott Marlowe
Cc: Paul Newman; pgsql general
Subject: Re: [GENERAL] Triggers and Multiple Schemas.

Paul,
What is the current schema layout for your db instances? I don't think
it's possible to share across db instances like this:

dbname1.myschema.sometable
dbname2.myschema.sometable

But you can share resources of the following type:

dbname.myschema1.sometable
dbname.myschema2.sometable
dbname.myschema2.sometable2
dbname.myschema2.sometable3

I think that it's a mis-statement to call each separate schema a DB, but

the group of:
dbname.myschema2.(collection of objects) is effectively a separate DB,
in that, the tables are what constitute a functional db.

so you can treat
dbname.myschema1.(...)
and
dbname.myschema2.(...)
as separate databases that share common resources, because they belong
to the same db instances, namely "dbname"