Re: Schema + User-Defined Data Type Indexing problems...

Chris Gamache <cgg007(at)yahoo(dot)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