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.gonzales@xxxxxxxxxxxxxx] 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"
