Paul Newman wrote:

Hi,

We run with multiple identical schemas in our db. Each schema actually represents a clients db. What we’d like to do is have a common schema where trigger functions and the like are held whilst each trigger defined against the tables is in there own particular schema. This would mean that there is one function per trigger type to maintain.

However at the moment we are placing the trigger functions within each schema along with trigger itself. The reason is that we don’t know of a function or a variable that says “Give me the schema of the trigger that is calling this function”. We are therefore having to write the function into every schema and then use set search_path =br1; as the first line. This is a real headache to us since we are intending on putting 200 – 300 schemas in one db.

My question is … is there such a function or variable ? …. Or is there a better for us to achieve this ?

Regards

Paul Newman

Paul,
When you say "multiple identical schemas" are they all separate explicit schemas? Or are they all under a general 'public' schema.
>From my understanding, when you create a new db instance, it's under the public level schema by default unless you create an explicit schema and subsequently a db instance - or several - therein, effectively establishing sibling db instances belonging to a single schema, I know at least that data in the form of table access is allowed across the siblings. I'd also assume that this would be the case for triggers and functions that could be identified or defined at the 'root' level schema.

Now I'm sure there is associated jargon with this type of hierarchical or tiered schema layout, so please don't anybody shoot me because of my analogy to 'root' level scenario.

I think this is a great opportunity for somebody to add additional insight with their experience with utilizing explicit schemas, rather than the default public schema.

We have to remember, that for every database instance, there is at least one schema to which it belongs, meaning that a schema and is a db container of sorts, there can be many database instances that exist in 1 schema to - typically public by default.

I know I'm opening up a big can of worms... but hey... let's have it ;)