Re: Schema boggle...

On Wed, 5 Nov 2003, Jan Wieck wrote:

> Chris Bowlby wrote:
>
> > Hi All,
> >
> > I work with Marc Fournier, for those who don't know, and have been
> > working extensively with the schemas feature for Hub.Org's new Account
> > Management package. Each client's data is stored in a seperate schema
> > set asside just for them (though they will never have direct access to
> > it, it helps us "balance" out the data. Each schema has the same set of
> > tables, with which different data is stored, depending on the client.
> >
> > I've run into two issues and I've been reading over the docs in
> > relation to schemas in the hopes that I could find a solution to my
> > problems. Here are the details of the issues:
> >
> > 1. The first issue I'm running with, is that if I have several schemas
> > with the following name:
> >
> > public
> > test_001
> > test_002
> > test_003
> >
> > the test_* schemas have the same tables listed in the their name
> > spaces, such that:
> >
> > Schema test_001 contains:
> >
> > test_contact;
> > test_domains;
> > test_accounts;
> > ...
> >
> > Schema test_002 contains:
> >
> > test_contact;
> > test_domains;
> > test_accounts;
> > ...
> >
> > etc.
> >
> > As you can see this is a nice, clean way to break down some datasets.
> > But, if I do:
> >
> > set search_path to public, test_001, test_002;
> >
> > I only get access to the tables in test_001 and public, the tables in
> > test_002 are not listed, and thus I do not see them on the screen while
> > doing a "\d".
> >
> > 2. Issue 2, is more a SQL issue then anything, but I'd like to be able
> > to do something to the affect of:
> >
> > SELECT * FROM test_*.test_domains ORDER BY domain_name;
> >
> > or better yet:
> >
> > SELECT * from public.domain_summary ds LEFT JOIN test_*.test_domains td
> > ON (td.domain_id = ds.id);
> >
> > This would mean I can run one query to get all of the information I
> > need, rather then having to run a query for each client that I have to
> > list domains for. If I've got 10,000 clients, that's going to take a ton
> > of time to get the results I'm looking for...
> >
> > Does anyone know if any of these issues have been addressed in 7.4? I
> > see some references to schema based changes, but no details on what
> > those changes were. If not, are there any plans to do something like
> > that?
>
> You can't use a wildcard like that, but you can create a view like
>
> CREATE VIEW public.all_test_domains AS
> SELECT * FROM test_001.test_domains
> UNION ALL
> SELECT * FROM test_002.test_domains;
>
> You have to change that view as customers (resp. schemas) come and go.

Oh, just thought of it ... I know there used to be limitation on this, but
I swore that they were removed ... how about a function? could you do
something like:

for i in `SELECT nspname FROM nspname WHERE nspname LIKE 'test_*'`

... I know functins returning multi-rows was a limitation in the past, but
didn't that *just* get corrected, or am I mis-remembering?