Re: Schema boggle... - Mailing list pgsql-hackers

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 *
FROMtest_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@Yahoo.com #