From: Igor Maciel Macaubas <igor@providerst.com.br>
To: pgsql-admin@postgresql.org
Subject: Database schemas: search_path
Date: Thu, 14 Oct 2004 09:42:45 -0300
Message-ID: <001601c4b1eb$50f45fc0$6801a8c0@providerst.local> (raw)
Hi all,
I've organized my database design in multiple schemas, and migrated my existing tables to different schemas by using a simple how-to I found in this list history.
But what I couldn't find was a way to make this schemata fully transparent to my application - which does selects directly to tables instead to schema.table. I know that I can always set the search_path environmnet variable, but I have to do it on each session. I'd like to know if there is a way to make my set search_path persistent on the database, no matter the session.
I believe that this is supported, but couldn't find out how to make it. Can someone help me?
Having to add the 'set search_path = xxx,yyyy,zzzzz' everytime I connect is pain for me and my application.
Another question I have is:
Let's say I have a simple database with 100 tables, divided in 10 schemas with 10 tables in each. Let's say that my last schema is 10th_schema and my last table is z_table (last by creation - is the most recent created table, so I assume it's the last table on the database). My search_path is ascending from the 1st_schema to the 10th_schema.
Would my querie times/performance on table z_table be faster if this table was created on the 1st_schema ?
How does postgres make this search on the search_path to find where a table is in?
Thanks for your help, cya!
Regards,
Igor
--
igor@providerst.com.br
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: igor@providerst.com.br
Subject: Re: Database schemas: search_path
In-Reply-To: <001601c4b1eb$50f45fc0$6801a8c0@providerst.local>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox