Thread: Inserting a record into another table ... inside a schema
Hi,
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 ?
Thanks
Paul Newman
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> 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> Voice: 503-667-4517 Fax: 503-667-8863