To: cgg007@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Schema + User-Defined Data Type Indexing problems... 
In-reply-to: <20040610132050.94570.qmail@web13801.mail.yahoo.com> 
References: <20040610132050.94570.qmail@web13801.mail.yahoo.com>
Comments: In-reply-to Chris Gamache <cgg007@yahoo.com>
	message dated "Thu, 10 Jun 2004 06:20:50 -0700"
Date: Thu, 10 Jun 2004 10:09:53 -0400
Message-ID: <24190.1086876593@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Chris Gamache <cgg007@yahoo.com> writes:
> Is it even possible to create an index that lives in a different
> schema from the table it is indexing?

It is not --- the index always lives in the same schema as its table.
However, I think that the real issue here is "where is the datatype?".
I'm assuming that you created both the datatype uuid and the opclass
for it in my_schema.  So, when working in another schema (with my_schema
not in the search path at all) you'd have had to say

	create table foo (my_uuid my_schema.uuid);

and if you then try to make an index you'll have to say

	create index fooi on foo (my_uuid my_schema.uuid_ops);

because no default opclass for uuid will be found in the search path.

In practice I'm not sure that this is really a situation that we need to
fret about, because using a datatype that isn't in your search path has
got notational problems that are orders of magnitude worse than this
one.  The functions and operators that do something useful with the
datatype would also have to be schema-qualified every time you use them.
This is perhaps tolerable for functions but it's quite unpleasant for
operators :-(  You can't write
	select * from foo where my_uuid = 'xxx';
instead
	select * from foo where my_uuid operator(my_schema.=) 'xxx';
Yech.  I think you'll end up putting uuid's schema in your search path
before long anyway.

			regards, tom lane