Re: Schema boggle...

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.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #