Inserting a record into another table ... inside a schema

On May 5, 2005, at 8:32 AM, Paul Newman wrote:

I have a public schema with all my tables in it.

I also have a schema for each company that uses our system .. so 300
companies = 300 schemas. One of the tables has a trigger that is
trying to insert/update another table that is inside the same schema.
So far so good. However when I update the base table in schema
“MySchema” the trigger tries to insert/update a record in the public
schema and not the “Current” schema (ie the schema of the original
table). Is there a system variable for “Current Schema” ? …. If not
what is the best way to achieve this ?

You want search_path -- see

http://www.postgresql.org/docs/8.0/interactive/ddl-schemas.html#DDL-
SCHEMAS-PATH

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

On Thu, 5 May 2005, John DeSoi wrote:

However when I update the base table in schema MySchema the trigger
tries to insert/update a record in the public schema and not the Current
schema (ie the schema of the original table). Is there a system variable
for Current Schema ? &. If not what is the best way to achieve this ?

You want search_path -- see

Pardon my ignorance, but why wouldn't it work to specify the schema with
the table; e.g., update myschema.table1?

Rich

--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
<http://www.appl-ecosys.com&gt; Voice: 503-667-4517 Fax: 503-667-8863

On May 5, 2005, at 11:27 AM, Rich Shepard wrote:

Pardon my ignorance, but why wouldn't it work to specify the schema
with
the table; e.g., update myschema.table1?

Sure. I actually started to write that, but then assumed this would be
known by anyone who created 300 schemas :).

It is certainly my preference over worrying about the search_path. But
if you are working in 300 schemas with the same structure I suspect you
are looking for ways to simplify the code without using fully qualified
tables.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

On Thu, 5 May 2005, John DeSoi wrote:

Sure. I actually started to write that, but then assumed this would be
known by anyone who created 300 schemas :).

John,

Ah, assumptions! :-)

It is certainly my preference over worrying about the search_path. But if
you are working in 300 schemas with the same structure I suspect you are
looking for ways to simplify the code without using fully qualified tables.

Hmmm-m-m. Perhaps you are correct. I'd have thought of a loop, but I don't
know the organization of those schema. Anyway, thanks for removing my
puzzlement.

Rich

--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
<http://www.appl-ecosys.com&gt; Voice: 503-667-4517 Fax: 503-667-8863